I came across a SQL function I was not familiar with: decode. I looked it up and immediately replaced it with a CASE statement (in addition to other code cleanup). I’m afraid I don’t understand the existence of Oracle SQL’s decode() function:
decode(expression, search, result [, search , result]... [, default] )
seems functionally equivalent to
CASE expression WHEN search THEN result [WHEN search THEN result]... [ELSE default] ENDexcept the CASE version
- has no limitation of “only” 255 total expression + search + result + default parameters
- is easier to read
- works in PL/SQL context
- is ANSI-compliant
The only “benefit” to decode I could find is that the decode function will attempt to convert all of the results to the type of the first result, while CASE just errors if you mix types. To me, that benefit would simply encourage sloppy coding and keep you from noticing buggy code as quickly.
So, what’s the point? Are their coders out there who “get” the function syntax more readily than the CASE syntax, so it helps their coding efficiency? Or is there a benefit I’m missing?