G-4240

🆓

Warning

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.

Rule Details

Severity Level: Critical
Quality Area: Reliability
Fixing times: 5 mins
Local-medium
Database Support: OracleDB (v9.0.1 +)

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

Non-Compliant Example

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

Issues

Line	Column	Message
1	8	Use CASE instead of NVL2.

Compliant Solution - ★★★★★

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;

References

same as Trivadis G-4240