Skip to content

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

Minor

Maintainability, Portability

Reason

decode is an Oracle Database 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
6
-- @formatter:off
select decode(ctry.country_code, constants_up.co_ctry_uk, constants_up.co_lang_english 
                               , constants_up.co_ctry_fr, constants_up.co_lang_french
                               , constants_up.co_ctry_de, constants_up.co_lang_german
                                                        , constants_up.co_lang_not_supported)
  from countries ctry;

null values can be compared in decode:

1
2
3
4
5
6
-- @formatter:off
select decode(ctry.country_code, constants_up.co_ctry_uk, constants_up.co_lang_english 
                               , constants_up.co_ctry_fr, constants_up.co_lang_french
                               , null                   , constants_up.co_lang_unknown
                                                        , constants_up.co_lang_not_supported)
  from countries ctry;

Example (good)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
select case ctry.country_code
          when constants_up.co_ctry_uk then
             constants_up.co_lang_english
          when constants_up.co_ctry_fr then
             constants_up.co_lang_french
          when constants_up.co_ctry_de then
             constants_up.co_lang_german
          else
             constants_up.co_lang_not_supported
       end
  from countries ctry;

Simple case can not compare null values, instead the searched case expression must be used:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
select case
          when ctry.country_code = constants_up.co_ctry_uk then
             constants_up.co_lang_english
          when ctry.country_code = constants_up.co_ctry_fr then
             constants_up.co_lang_french
          when ctry.country_code is null then
             constants_up.co_lang_unknown
          else
             constants_up.co_lang_not_supported
       end
  from countries ctry;