NVL2 function in Oracle

This SQL tutorial provides explanations, examples for NVL2 function in Oracle

NVL2 function is a nesting function .It examines the first impression, if the the first impression is not null ,then NVL2 function returns the second expression. if the first impression is null, then it return the third expression.

expr1: it is the source values or expression which may contain null. You can provide column name,function on the column name
expr2: This is the value of the expression which is returned if the expr1 is not null
expr3: This is the value of the expression which is returned if the expr1 is null

The argument expr1 can have any datatype. The arguments expr2 and expr3 can have any datatypes except LONG.

SQL> select nvl2(user_name,1,2) from apps.fnd_user where rownum < 5;

NVL2(USER_NAME,1,2)
-------------------
1
1
1
1

SQL> select nvl2(end_date,1,2) from apps.fnd_user where rownum < 5;

NVL2(END_DATE,1,2)
------------------
1
2
1
1

If the datatypes of expr2 and expr3 are different:

If expr2 is character data, then Oracle Database converts expr3 to the datatype of expr2 before comparing them unless expr3 is a null constant. In that case, a datatype conversion is not necessary. Oracle returns VARCHAR2 in the character set of expr2.

If expr2 is numeric, then Oracle determines which argument has the highest numeric precedence, implicitly converts the other argument to that datatype, and returns that datatype.

The data type of the return type is always the same as the data type of expr2 unless expr2 is character data, which case return value is always varchar2

SQL> select nvl2(end_date,1,'a') from apps.fnd_user where rownum < 5;
select nvl2(end_date,1,'a') from apps.fnd_user where rownum < 5
*
ERROR at line 1:
ORA-01722: invalid number

SQL> select nvl2(end_date,'a',1) from apps.fnd_user where rownum < 5;

N
-
a
1
a
a

Usage

SELECT last_name, salary, NVL2(commission_pct,
salary + (salary * commission_pct), salary) income
FROM employees WHERE last_name like 'B%'
ORDER BY last_name;

Related Articles

Related Posts

Leave a Comment Cancel Reply