2

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
cjd143SD
  • 859
  • 7
  • 17
  • 28

3 Answers3

2

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

Andrew
  • 26,629
  • 5
  • 63
  • 86
0

try

SELECT 0, to_char(sysdate-2, 'yyyymmdd') || '0000' FROM DUAL WHERE 0 = 
(SELECT count(*) FROM TESTDATA WHERE timestamp = to_char(sysdate-2, 'yyyymmdd') || '0000')
UNION
SELECT count(*) as MYCNT, timestamp FROM TESTDATA WHERE timestamp = to_char(sysdate-2, 'yyyymmdd') || '0000' group by timestamp

this return the real data if it is present and if not the default

Yahia
  • 69,653
  • 9
  • 115
  • 144
0

Try this:

 SELECT count(*) as MYCNT, timestamp 
FROM TESTDATA 
WHERE timestamp = to_char(sysdate-2, 'yyyymmdd') || '0000' 
GROUP BY timestamp 
UNION
SELECT 0, '201107250000'
  FROM dual
Chandu
  • 81,493
  • 19
  • 133
  • 134