G-4220

🆓

Warning

Try to use CASE rather than DECODE.

Rule Details

Severity Level: Major
Quality Area: Maintainability
Fixing times: 5 mins
Quick Fix | Local-medium
Database Support: OracleDB (v9.0.1 +)

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

Non-Compliant Example

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;

Issues

Line	Column	Message
1	8	Use CASE instead of DECODE.

null values can be compared in decode:

-- @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;

Compliant Solution - ★★★★★

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:

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;

Parameters

Use parameters to customize the rule to your needs.

Parameter	Description	Default Value
DisableAllQuickFix	Comma-separated list 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-4220
same as plsql:DecodeFunctionUsageCheck