DB2 Exclude Nulls or set to arbitrary date

Question

I am having a very difficult time handling null returns in DB2. I've tried IFNULL AND COALESCE functions, but I still end up getting values in the return.

Here is the relevant segment of the query:

COALESCE (
    DATE (
        SUBSTR (DIGITS (PODATE),1,2) || char ('/') ||
        SUBSTR (DIGITS (PODATE),3,2) || char ('/') ||
        SUBSTR (DIGITS (PODATE),5,2)
    ),
    DATE ('12/31/99')
) AS PODATE

This returns, but I still get nulls. Any suggestions?

Answer 1

I would, for a start, not coalesce a per-row function like date(). You're better off testing the actual field for NULL as in the following example:

create table a (podate varchar(8));                                          
insert into a (podate) values ('20090101');
insert into a (podate) values ('20090102');
insert into a (podate) values ('20090103');
insert into a (podate) values (null);
commit;
select
    case when podate is null
        then date('2000-01-01')
        else date(substr(podate,1,4) || '-' ||
                  substr(podate,5,2) || '-' ||
                  substr(podate,7,2))
    end as mydate
from a;
drop table a;
commit;

which outputs:

---------
MYDATE
----------
2009-01-01
2009-01-02
2009-01-03
2000-01-01
DSNE610I NUMBER OF ROWS DISPLAYED IS 4

In your case, the code would look something like:

select case when podate is null
       then date('12/31/99')
       else date (substr (podate,1,2) || char ('/') ||
                  substr (podate,3,2) || char ('/') ||
                  substr (podate,5,2)
       end

I haven't tested your specific case since my DB/2 is not set up for US date formats. That's something else to look out for as well. This may not work that well on platforms not using US date formats.

Ideally, dates should be stored in the database as dates, not strings. In other words, the conversion costs should be once when the data enters a table, not the millions of times you're going to extract it. Then your code becomes a simpler and faster:

select case when podate is null
       then date('12/31/99')
       else podate
       end

or, better if you're never going to have that sentinel date as a real date, don't store NULLs at all. Instead, convert them to date('12/31/99') as they go into the tables, and use:

select podate

It doesn't get much simpler or faster than that (assuming you're comfortable with a non-NULL sentinel).

Answer 2

You might try with an ISO-formatted string as Pax implicitly suggested instead of the locale-dependent one you used. This SQL statement will always work, regardless of your locale date format setting.

SELECT DATE('2009-04-01')
FROM SYSIBM.SYSDUMMY1

On a other side, to breakup a numeric field, using division and modulo is sometime a nice trick. You might then use numeric comparison on it.

With the sample data Pax provided :

SELECT 
    MOD(PODATE / 10000, 100) AS YEAR,
    MOD(PODATE / 100, 100) AS MONTH,
    MOD(PODATE, 100) AS DAY
FROM (VALUES (991231), (090101), (null)) AS A (PODATE)

But as Pax said : Dates should be stored in the database as dates, not strings. Same for numerical quantities as numerical types, etc.