Path: news.f.de.plusline.net!news-fra1.dfn.de!newsfeed.hanau.net!news.tiscali.de!tiscali!newsfeed1.ip.tiscali.net!proxad.net!cleanfeed3-b.proxad.net!nnrp1-1.free.fr!not-for-mail
From: "Michel Cadot" <micadot{at}altern{dot}org>
Newsgroups: comp.databases.oracle.tools
References: <1173420147.016604.284770@p10g2000cwp.googlegroups.com>
Subject: Re: displaying a column in report which returns null value
Date: Fri, 9 Mar 2007 16:58:48 +0100
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3028
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
Lines: 43
Message-ID: <45f18437$0$2146$426a74cc@news.free.fr>
Organization: Guest of ProXad - France
NNTP-Posting-Date: 09 Mar 2007 16:58:47 MET
NNTP-Posting-Host: 82.67.171.166
X-Trace: 1173455927 news-1.free.fr 2146 82.67.171.166:2154
X-Complaints-To: abuse@proxad.net
Xref: news.f.de.plusline.net comp.databases.oracle.tools:35580


<dtrusha@gmail.com> a écrit dans le message de news: 1173420147.016604.284770@p10g2000cwp.googlegroups.com...
| Hi
| I have a simple report with two column. The query is as below.
| SELECT decode(caste,1,'SC/ST',2,'General','3','PWD')  fcastepi,
| nvl(count(*),0) fcapi
|   FROM applicationformdtl
| WHERE year = :p_year
|      AND selected = 1
|     AND PROGCD = 'PRM'
| GROUP BY caste
|
| This query returns values for SC/ST and General as there is no
| matching data for the PWD. But I want to display PWD with value 0 in
| the report. How to do this? Can anyone help me in this matter at the
| earliest?
|
| The required output is
| SC/ST   100
| General 300
| PWD        0
|
| Regards
| Trusha
|

Something like:

SELECT decode(b.caste,1,'SC/ST',2,'General','3','PWD')  fcastepi,
nvl(count(a.caste),0) fcapi
   FROM applicationformdtl a
, ( select rownum caste from dual connect by level <= 3 ) b
WHERE year (+) = :p_year
      AND selected (+) = 1
     AND PROGCD (+) = 'PRM'
and a.caste (+) = b.caste
 GROUP BY b.caste
/

Regards
Michel Cadot