codeling 1264 - 5430
@2019-06-14 09:04:21

Always use CASE instead of NVL2 if parameter 2 or 3 of NVL2 is either a function call or a SELECT statement.

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


select nvl2(dummy, 'yes', 'no') from dual;


select case

             when dummy is null then 'no'

             else 'yes'

from dual;

codeling 1264 - 5430
@2019-06-14 09:08:59

The DECODE function decodes an expression in a way similar to the IF-THEN-ELSE logic used in various languages. The DECODE function decodes expression after comparing it to each search value. If the expression is the same as search, result is returned.

If the default value is omitted, a null value is returned where a search value does not match any of the result values.

DECODE(col|expression, search1, result1 
 [, search2, result2,...,][, default])

codeling 1264 - 5430
@2019-06-14 09:25:43

Try to use CASE rather than DECODE.

DECODE is an old function that has been replaced by the easier-to-understand and more common CASE function. Contrary to the DECODE statement CASE may also be used directly within PL/SQL. 


select decode(dummy, 'A', 1,
                     'B', 2, 
                     'C', 3, 
                     'D', 4,
from dual;


select case dummy
                 when 'A' then 1
                 when 'B' then 2
                 when 'C' then 3
                 when 'D' then 4
                 else 5
from dual;

codeling 1264 - 5430
@2019-06-14 09:28:31

Try to use CASE rather than an IF statement with multiple ELSIF paths. IF statements containing multiple ELSIF tend to become complex quickly. 

Users browsing this topic