Query returns no row when no data

user_5169691 0
Viewed 85

I am running the below query which returns blank row when there is no data. When there is data, the query will return the correct count. Is there a way to return 0 instead of a blank row?

Database is Oracle

select alert_count 
from ALERT_LOG 
where alert_name = 'BUCKET' 
and create_date>= trunc(sysdate)

Any help will be greatly appreciated.

Thanks

oracle sql
3 Answers

Aggregates always return a row (unless there is a having clause that excludes all results), so use this:

select nvl(sum(alert_count),0)
from   alert_log
where  alert_name = 'BUCKET' and create_date>= trunc(sysdate);
user_2304710

You can use coalesce to return 0 instead null.

select coalesce(sum(alert_count),0)
from alert_log
where alert_name = 'BUCKET' 
and create_date>= trunc(sysdate)
user_86913190

If there is no aggregation and you just selecting alert_count then neither nvl() nor coalesce() will work since there is no row at all. You can use union all to get 0 as default result in this scenario. Try this:

select alert_count from ALERT_LOG where alert_name = 'BUCKET' and create_date>= trunc(sysdate)
union all
select 0 from dual where not exists (select alert_count from ALERT_LOG where alert_name = 'BUCKET' and create_date>= trunc(sysdate))

If you are using aggregation then you can use nvl(count(alert_count),0 ) or coalesce(count(alert_count),0 ).

user_86516010
Related