NVL2 lets you compute a value based on whether a specified
    expression is null or not null. If the first value expression
    is not null then the second value expression is returned as
    the function result. Otherwise, the final value expression is
    returned. The data type function is derived as a common data type
    of the second and third value expressions.

    For example, when the JOB_END date in JOB_HISTORY is NULL then
    that indicates the current job for that employee. The following
    example uses NVL2 to annotate the output from a query on JOB_
    HISTORY displaying either "current job" or "prior job" based on
    the NULL attribute of the JOB_END column.

    SQL> select employee_id, job_start, job_end,
    cont> NVL2 (job_end, 'prior job', 'current job')
    cont> from job_history
    cont> where employee_id < '00180'
    cont> order by employee_id, job_start;
     EMPLOYEE_ID   JOB_START     JOB_END
     00164          5-Jul-1980   20-Sep-1981   prior job
     00164         21-Sep-1981   NULL          current job
     00165          1-Jul-1975    4-Sep-1977   prior job
     00165          5-Sep-1977    7-Apr-1979   prior job
     00165          8-Apr-1979    7-Mar-1981   prior job
     00165          8-Mar-1981   NULL          current job
       .
       .
       .

    The following example shows whether the income of some employees
    is made up of SALARY plus COMMISSION, or just SALARY, depending
    on whether the COMMISSION_PCT column of EMPLOYEES is null or not.

    SQL> SELECT last_name, salary_amount,
    cont>       NVL2 (commission_pct,
    cont>             salary_amount + (salary_amount * commission_pct),
    cont>             salary_amount) as Income edit using SALARY
    cont> FROM employees e, salary_history sh
    cont> WHERE last_name like 'B%'
    cont>   and e.employee_id = sh.employee_id
    cont>   and salary_end is null
    cont> ORDER BY last_name;
     E.LAST_NAME      SH.SALARY_AMOUNT        INCOME
     Babbin                 $20,150.00    $20,956.00
     Bartlett               $14,817.00    $15,261.51
     Bartlett               $38,223.00    $38,987.46
     Belliveau              $54,649.00    $55,741.98
     Blount                 $63,080.00    $64,341.60
     Boyd                   $30,275.00    $30,275.00
     Boyd                   $24,166.00    $24,166.00
     Brown                  $50,357.00    $50,357.00
     Burton                 $23,053.00    $23,053.00
    9 rows selected
    SQL>