Oracle replacing null using NVL check for empty rows

Question

I am running a query to retrieve an integer but want to put a check for nulls. If null I want the query to return 0. How can I do that? I tried this below but it's not working

select NVL(A_COUNT, 0) from MYTABLE where VEH_YEAR = '2003';

If A_COUNT is null I want the query to return 0. In the above case I don't have a value 2003 in VEH_YEAR column. The query works if I have a value 2003 in VEH_YEAR column but A_COUNT is null.

Answer 1

Better version of your uery would be, using an Aggregate function like MAX before using NVL.

select NVL(MAX(A_COUNT),0) from MYTABLE where VEH_YEAR = '2003';