G-4240: Always use a CASE instead of a NVL2 command if parameter 2 or 3 of NVL2 is either a function call or a SELECT statement.

Critical

Efficiency, Reliability

Reason

The nvl2 function always evaluates all parameters before deciding which one to use. This can be harmful, if parameter 2 or 3 is either a function call or a select statement, as they will be executed regardless of whether parameter 1 contains a null value or not.

Example (bad)

1
2
3
select nvl2(dummy, my_package.expensive_nn(value_in => dummy), 
                   my_package.expensive_null(value_in => dummy))
  from dual;

Example (good)

1
2
3
4
5
6
7
select case
          when dummy is null then 
             my_package.expensive_null(value_in => dummy)
          else 
             my_package.expensive_nn(value_in => dummy)
       end
from dual;