NVL2As an enhancement over NVL, Oracle introduced a function to substitute value not only for NULL columns values but also for NOT NULL columns. NVL2 function can be used to substitute an alternate value for NULL as well as non NULL value.
The SELECT statement below would display 'Bench' if the JOB_CODE for an employee is NULL. For a definite not null value of JOB CODE, it would show constant value 'Job Assigned'.
NVL2( string1, value_if_NOT_null, value_if_null )
Read more: Conv. Func.
SQL> SELECT NVL2(JOB_CODE, 'Job Assigned', 'Bench') FROM employees;