G-4220: Try to use CASE rather than DECODE.
Major
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)
| -- @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
:
| -- @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)
| 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;
|