Term: NVL2

Definition:
The Oracle PL/SQL NVL2 function is a null handling function which returns alternate values for both NULL and NOT NULL values of a column. It accepts three arguments, the first being the column name, the second is the alternate value of the column when it is NOT NULL, and the third is the alternate value when the column value is NULL.

Example Syntax:

NVL2(n,x,y)

x is alternate value when n is NOT NULL
y is alternate value when n is NULL.

Note: x and y may have any data type except LONG. The data type of 'x' determines the data type of the output. If it is a character data type, then 'y' is converted to character before comparison, if 'y' is not a constant data. If 'x' is a number, then Oracle casts both 'x' and 'y' to the data type of the higher precedence.

Example Usage:

The SQL query below displays the message 'JOB' for employees who have a valid JOB ID. For those who don't have a JOB ID assigned, the query displays BENCH.

SQL> SELECT EMPNO,JOB, NVL2(JOB, 'JOB', 'BENCH')

           FROM EMPLOYEE

/



     EMPNO JOB        NVL2(

---------- ---------- -----

       100 MGR        JOB

       110 TECH       JOB

       120            BENCH

       130            BENCH

       140 MGR        JOB

       150 TECH       JOB

       160            BENCH

       170 HR         JOB

       180 MGR        JOB



9 rows selected.