db2 query

Posted on 2011-04-27
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(*))
       WHERE CO_ID IN ('01','02')
       AND POL_CSTAT_CD = 'E'
       AND POL_STAT_CHNG_DT BETWEEN '2011-04-01' AND '2011-04-27'
Question by:dmala2
    LVL 37

    Expert Comment

    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
           POL_CSTAT_CD = 'E'
           AND POL_STAT_CHNG_DT BETWEEN '2011-04-01' AND '2011-04-27'
           GROUP BY t2.CO_ID
    LVL 40

    Accepted Solution

    try this.
             SUBSTR('LAPSED     ',1,11), 
             SUM(CASE POL_CSTAT_CD 
                   WHEN 'E' THEN 1 
                   ELSE 0 
        FROM JIT0.TPOL 
       WHERE CO_ID IN ('01','02') 
             AND POL_STAT_CHNG_DT BETWEEN '2011-04-01' AND '2011-04-27' 

    Open in new window


    Author Comment

    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!

    Author Closing Comment

    Thank you!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    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…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now