Problem returning

From: Andrew Ford <Andrew.Ford_at_spis.pnn.police.uk>
Date: 27 Sep 2002 07:19:29 -0700
Message-ID: <a298fde5.0209270619.6c954322@posting.google.com>

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 Received on Fri Sep 27 2002 - 09:19:29 CDT