G-4220
🆓Warning
Try to use CASE rather than DECODE.
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. |
Explanation
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;Explanation
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;References
- same as Trivadis G-4220
- same as plsql:DecodeFunctionUsageCheck