Decoding DECODE

I've never been a fan of DECODE. I find it difficult to read, especially past the (EXPRESSION, MATCH_TO_EXPRESSION, RESULT_IF_MATCHED, ELSE_RETURN_VALUE), i.e. 4 spots there. A friend of mine was trying to...decode a long DECODE expression, so I put on my Cary Millsap hat and tried to break it down.

Despite never having seen Cary speak, I love that he always includes Teacher in his bio. Those that present are in fact teachers, but with that inclusion, I think Cary takes it a step further.



(I plan on changing that streak this year, see Tools, Tools, Tools!)

So here's what I said, maybe it can help some newbie out there get started. Me, I encourage people to avoid the statement suggesting the CASE statement instead...mostly for it's readability (naturally I have seen that abused as well, oh well).

Here's a mock statement:

DECODE(my_expression,0,0,NULL,NULL,(another_expresssion/my_expression))

First, format the dang thing. Put some white space in there.

DECODE( my_expression, 0, 0, NULL, NULL, ( another_expresssion / my_expression ) )

We're not exactly tuning, but readability goes a long way.

Now, let's look at the picture defined in the docs:



Just for simplicity's sake, I'm going to spell it out:

DECODE( POSITION_1, POSITION_2, POSITION_3, POSITION_4, POSITION_5, POSITION_6 )

At least to me, that helps. Up to 4 places, this probably isn't necessary, but as it gets long (and I've seen some whoppers), it's good to be able to draw it out.

What does that mean? If POSITION_1 = POSITION_2, then use the value from POSITION_3. If POSITION_1 = POSITION_4, use the value from POSITION_5. The default will be POSITION_6.

Written out in CASE format, it would look like this:

CASE
  WHEN my_expression /* POSITION_1 */ = 0 /* POSITION_2 */
  THEN 0 /* POSITION_3 */
  WHEN my_expression /* POSITION_1 */ IS NULL /* POSITION_4 */
  THEN NULL
  ELSE my_expression / another_expression /* POSITION_6 */
END

I write this up because I got the "You should be a teacher" comment at the end, which I took as a great compliment.