Oracle NVL2 function

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

Advertisements

Author: sqlandplsql

To learn more about the Oracle, SQL, PL SQL, Performance Tuning, Database Modeling, Ubuntu, MySQL etc .Suggestions, comments, feedbacks and referrals are highly appreciated.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s