Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Problem returning

Re: Problem returning

From: Ed Prochak <ed.prochak_at_magicinterface.com>
Date: Fri, 27 Sep 2002 18:35:53 GMT
Message-ID: <3D94D20D.F25887BD@magicinterface.com>


Andrew Ford wrote:
>
> Can anyone help?
>
> I have written the following query based on a decode statement that
> will do counts on certain grouped rows. I have a problem in that when
> the decode evaluates a blank column i.e. ... null, 'SPACES' nothing is
> shown in the results set i.e. SPACES 0. This is due to there being
> no rows that satisfy the query i.e. count=0. However, if I change the
> test data and allow a row to satisfy the query i.e. there is a null
> field in the results then SPACES row entry is shown in the result i.e.
> SPACES 1. Question is how would you change the query so that you
> would get an entry in the results when COUNT = 0 i.e. SPACES 0 ?
>
> SELECT DECODE(SUBSTR(tab1.col1,1,1),
> 'L', 'LOTH', 'S', 'STRAT',
> 'D', 'DUMF', 'C', 'CEN', 'F', 'FIF',
> 'T', 'TAYS', 'G', 'GRAMPS', 'N', 'NORTH',
> null , 'SPACES', 'OTHERS')DECODE,
> COUNT(tab1.col4) COUNT
> FROM tab1
> WHERE tab1.col2 <> 'B'
> AND UPPER(tab1.col3) LIKE '%F%'
> GROUP BY DECODE(SUBSTR(tab1.col1,1,1),
> 'L', 'LOTH', 'S', 'STRAT',
> 'D', 'DUMF', 'C', 'CEN', 'F', 'FIF',
> 'T', 'TAYS', 'G', 'GRAMPS', 'N', 'NORTH',
> null , 'SPACES', 'OTHERS');
>
> Result
>
> DECODE COUNT
> ------ ----------
> CEN 25
> DUMF 31
> FIF 24
> GRAMPS 29
> LOTH 35
> NORTH 22
> OTHERS 35
> STRAT 577
> TAYS 35
>
> Cheers
> Andrew

to get what you want (a row with "SPACES 0"),  then these conditions must be met:

there is a row in tab1 that looks like this:

col1	col2	col3	col4
----	----	----	----
NULL	<>'B'	%F%	NULL

Does such a row exist in your table?

-- 
Edward J. Prochak   --- Magic Interface, Ltd.
Ofc: 440-498-3700   --- 7295 Popham Place, Solon, OH 44139
on the web at       --- http://www.magicinterface.com
email: ed.prochak_at_magicinterface.com
Received on Fri Sep 27 2002 - 13:35:53 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US