dmala2
asked on
db2 query
I'm trying to unload some counts using dsntiaul, i think we are on version 8 for db2 and wondering how i can force it to return me the 0 count
so in this query below, i have data for company '02' but not for the status E so I would like it to rertun 0, it's only showing the count for existing '01'
SELECT,CO_ID, SUBSTR(';', 1, 1),
SUBSTR('LAPSED ', 1,11),
CHAR( COUNT(*))
FROM JIT0.TPOL
WHERE CO_ID IN ('01','02')
AND POL_CSTAT_CD = 'E'
AND POL_STAT_CHNG_DT BETWEEN '2011-04-01' AND '2011-04-27'
GROUP BY CO_ID
;
so in this query below, i have data for company '02' but not for the status E so I would like it to rertun 0, it's only showing the count for existing '01'
SELECT,CO_ID, SUBSTR(';', 1, 1),
SUBSTR('LAPSED ', 1,11),
CHAR( COUNT(*))
FROM JIT0.TPOL
WHERE CO_ID IN ('01','02')
AND POL_CSTAT_CD = 'E'
AND POL_STAT_CHNG_DT BETWEEN '2011-04-01' AND '2011-04-27'
GROUP BY CO_ID
;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
AWESOME! The second one works great! I just put a char back around it so the unload was nice and formatted. Too cool, I really appreciate it!
ASKER
Thank you!
this can also be done like this
SELECT t2.CO_ID, SUBSTR(';', 1, 1),
SUBSTR('LAPSED ', 1,11),
CHAR( COUNT(*))
FROM (select '01' co_id from sysibm.sysdummy1 union all select '02' from sysibm.sysdummy1) t2
left outer join JIT0.TPOL t1 on t1.co_id = t2.co_id
WHERE
POL_CSTAT_CD = 'E'
AND POL_STAT_CHNG_DT BETWEEN '2011-04-01' AND '2011-04-27'
GROUP BY t2.CO_ID
;