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.

SELECT NVL((select A_COUNT from MYTABLE where VEH_YEAR = '2003'), 0) A_COUNT from dual; This worked — yalkris, Apr 30 '15 at 23:13

score 3 · Accepted Answer · answered May 01 '15 at 04:51

3

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';

answered May 01 '15 at 04:51

Maheswaran Ravisankar

17,652
6
47
69

score 0 · Answer 2 · answered Apr 30 '15 at 23:15

0

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

This worked.

answered Apr 30 '15 at 23:15

yalkris

2,596
5
31
51

Oracle replacing null using NVL check for empty rows

2 Answers2