Search code examples
sqloracle-databasenvl

oracle query help using NVL similar function to set default

How do I rewrite this oracle query, in the case I have no rows returned and want to hardcode a default value of '0' for a count and the sysdate information?

My query now will give me this if there is no data:

 1* SELECT count(*) as MYCNT, timestamp FROM TESTDATA WHERE timestamp = to_char(sysdate-2, 'yyyymmdd') || '0000' group by timestamp
SQL> /

no rows selected

Here, I tried NVL, but not getting expected output:

1* select nvl(count(*), 0) as MYCNT, to_char(sysdate-2, 'yyyymmdd') || '0000' from TESTDATA WHERE timestamp = to_char(sysdate-2, 'yyyymmdd') || '0000' group by timestamp
SQL> /

no rows selected

Want to see something like this:

MYCNT    TIMESTMP
-----    --------
0        201107250000
Solution

  • The group by is preventing this from just working by defult.

    select count(*) ,  to_char(sysdate-2, 'yyyymmdd') || '0000' as timestamp 
from dual where 1=0

    You can see this is the case by comparing the output to:

    select count(*) ,  to_char(sysdate-2, 'yyyymmdd') || '0000' as timestamp 
from dual where 1=0
group by  to_char(sysdate-2, 'yyyymmdd') || '0000'

    Second version returns blank, first version returns 0 and the timestamp