Using NVL2 in PLSQL Query

The Oracle/PLSQL NVL2 function extends the functionality found in the NVL function. It lets you substitutes a value when a null value is encountered as well as when a non-null value is encountered.

The syntax for the NVL2 function in Oracle/PLSQL is:

NVL2( string1, value_if_not_null, value_if_null )

string1

The string to test for a null value.

value_if_not_null

The value returned if string1 is not null.

value_if_null

The value returned if string1 is null.

The NVL2 function returns a substitute value.

select ENAME, COMM, SAL,

NVL2(COMM,SAL,0) from EMP

where ENAME in ('KING','ALLEN', 'WARD', 'SCOTT', 'TURNER');

ENAME         COMM        SAL              NVL2(COMM,SAL,0)

KING               (null)            5000             0

SCOTT           (null)            3000             0

ALLEN           300              1600            1600

WARD            500              1250            1250

TURNER        0                   1500            1500