[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

db2 query

Posted on 2011-04-27
4
Medium Priority
?
589 Views
Last Modified: 2012-05-11
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
       ;
0
Comment
Question by:dmala2
  • 2
4 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 35479712
you will have to use a left outer join to a table that has a list of the statuses
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
       ;
0
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 35479965
try this.
SELECT CO_ID, 
         SUBSTR(';',1,1), 
         SUBSTR('LAPSED     ',1,11), 
         SUM(CASE POL_CSTAT_CD 
               WHEN 'E' THEN 1 
               ELSE 0 
             END) 
    FROM JIT0.TPOL 
   WHERE CO_ID IN ('01','02') 
         AND POL_STAT_CHNG_DT BETWEEN '2011-04-01' AND '2011-04-27' 
GROUP BY CO_ID

Open in new window

0
 

Author Comment

by:dmala2
ID: 35480064
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!
0
 

Author Closing Comment

by:dmala2
ID: 35480170
Thank you!
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Suggested Courses
Course of the Month20 days, 6 hours left to enroll

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question