Decoding SQL’s Decode

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

Gorilla Scratching Head

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

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

—jhunterj

One thought on “Decoding SQL’s Decode

  1. The best answer from elsewhere seems to be that Oracle SQL implemented decode() in an earlier version than it implemented the ANSI CASE.

Leave a Reply

Your email address will not be published. Required fields are marked *

*


5 + = eleven

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>