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
Single row functions in Sql
NULLIF function in Oracle
Update statement in oracle
Coalesce Function in Oracle
Oracle LISTAGG Function
oracle date functions