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.
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 |
★★★★★
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;
Parameters
Use parameters to customize the rule to your needs.
Parameter | Description | Default Value |
---|---|---|
DisableAllQuickFix | Comma-separated ist of rules for which a quick fix should not be applied to all the problems in a file. | Core G-3130 |
References
- same as Trivadis G-4240