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
– yalkris, Commented Apr 30, 2015 at 23:13

Maheswaran Ravisankar · Accepted Answer · 2015-05-01 04:51:35Z

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 1, 2015 at 4:51

Maheswaran Ravisankar

17.9k6 gold badges50 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

yalkris · Accepted Answer · 2015-04-30 23:15:28Z

0

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

This worked.

answered Apr 30, 2015 at 23:15

yalkris

2,7155 gold badges34 silver badges51 bronze badges

Collectives™ on Stack Overflow

Oracle replacing null using NVL check for empty rows

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related