The NVL2 is an extended version of NVL. It takes three arguments.

Syntax:-

NVL2(expression,value1,value2)

Note:- All expression and values must be of compatible data types.

If expression is not null then
return value1
else
return value2

Examples :-

create table salary(no number, sal number,bonus number);
insert into salary values(1,1000,1000);
insert into salary values(2,2000,1000);
insert into salary values(3,3000,3000);
insert into salary values(4,null,4000);
commit;

select s.*, nvl2(s.sal,s.bonus,0) as nvl2value from salary s;

NO SAL BONUS  NVL2VALUE
— — —– ———————–
1 1000  1000       1000
2 2000  1000       1000
3 3000  3000       3000
4 null      4000      0

Related Posts

NVL
NULL
COALESCE