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]
END

By Steven Straiton (originally posted to Flickr as Gorilla) [CC-BY-2.0], via Wikimedia Commons

• 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?

—jhunterj