Skip to content

G-4220: Try to use CASE rather than DECODE.

Minor

Maintainability, Portability

Reason

decode is an ORACLE specific function hard to understand and restricted to SQL only. The “newer” case function is much more common, has a better readability and may be used within PL/SQL too. Be careful that decode can handle null values, which the simple case cannot - for such cases you must use the searched case and is null instead.

Example (bad)

1
2
3
4
5
select decode(dummy, 'X', 1 
                   , 'Y', 2
                   , 'Z', 3
                        , 0)
  from dual;

Example (good)

1
2
3
4
5
6
7
select case dummy
          when 'X' then 1
          when 'Y' then 2
          when 'Z' then 3
          else 0
       end
  from dual;

Example (bad)

1
2
3
4
5
select decode(dummy,  'X',  1 
                   ,  'Y',  2
                   , null, -1
                         ,  0)
  from dual;

Example (good)

1
2
3
4
5
6
7
select case
          when dummy = 'X'   then  1
          when dummy = 'Y'   then  2
          when dummy is null then -1
          else 0
       end
  from dual;