MainframeSupports
tip week 2/2017:

Are you crazy about using CASE in SQL, but at the same time you are a little tired of all the typing just to do even simple translations, then DECODE is really something for you. CASE is a kind of expression while DECODE is a so-called builtin function. Let me show you an example:

SELECT CASE HOUR(CURRENT TIME) WHEN 12 THEN 'LUNCH' WHEN 16 THEN 'HOME' ELSE 'WORK' END
     , DECODE(HOUR(CURRENT TIME), 12, 'LUNCH', 16, 'HOME', 'WORK')
FROM SYSIBM.SYSDUMMY1

The first parameter for DECODE is the expression to be translated. Second and third parameter is the first pair of translation from and to values. After the first translation pair you can specify as many translation pairs as you like. After the last translation pair you may specify a value that is used if none of the from values in the translation pairs were matched, just like you use ELSE in a CASE. And exactly like in a CASE where ELSE is missing DECODE will return NULL if the last parameter is not specified.

The limitation in using DECODE compared to CASE is that you cannot rewrite a CASE WHEN predicate to a DECODE if predicate is not a expression = expression. In those cases you still have to use good old CASE. As you can see in the example it is shorter to write a DECODE instead of a CASE especially if you have many translation pairs.

Previous tip in english        Forrige danske tip        Tip list