As 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.


NVL2( string1, value_if_NOT_null, value_if_null )
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'.
SQL> SELECT NVL2(JOB_CODE, 'Job Assigned', 'Bench')
FROM employees;
Read more: Conv. Func.
NULL-values General-Functions NV2

To check your knowledge the following tests are recommended:
Check your sql sql skills.