ORACLENERD: Decoding DECODE

Decoding DECODE

Monday, March 28, 2011

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.

Labels: decode, howto, oradb, sql

¶ 3/28/2011 03:59:00 PM

Comments:

Kids today don't know how lucky they are. DECODE was all we had in the early days.

You can use a CASE with LIKE or BETWEEN. We had to use really convoluted ASCII to convert the string to a number, then do a subtraction and a SIGN to change a range of values to a single value.

And we had to program it all with our noses because we hadn't evolved fingers yet. No wait, I think I'm making that last bit up.

# posted by

Gary Myers : March 28, 2011 at 4:42 PM

# posted by

Gary Myers : March 28, 2011 at 4:42 PM

They've got it easy now. They can look up syntax online. In my day you had to shout around the office to see who had the manual so you could look it up in a paper manual... :)

Cheers

Tim...

# posted by

Tim... : March 28, 2011 at 5:04 PM

Try putting NULL in position 1 and position 2. Then DECODE and CASE won't give the same results. That's the big advantage of DECODE: it's the only time NULL = NULL.

# posted by

Stew Ashton : March 28, 2011 at 6:00 PM

Why did you make it more complicate than it seems? What am I missing.

WITH a
AS (SELECT 0 x FROM DUAL
UNION ALL
SELECT NULL x FROM DUAL
UNION ALL
SELECT 9 x FROM DUAL)
SELECT
DECODE (x, 0,0, NULL,NULL, (27/x))
FROM a;

WITH a
AS (SELECT 0 x FROM DUAL
UNION ALL
SELECT NULL x FROM DUAL
UNION ALL
SELECT 9 x FROM DUAL)
SELECT
(CASE x
WHEN 0 THEN 0
WHEN NULL THEN NULL
ELSE 27 / x
END)
FROM a;

mdinh

# posted by

Anonymous : March 28, 2011 at 6:32 PM

All you young whipper snappers. Back in the old days, we had to walk to school in the snow... up hill.... both ways....

oh wait, I am a young whipper snapper.

# posted by

Tom : March 28, 2011 at 9:58 PM

Good call on the whitespace. I tend to take it a bit further (using those leading commas that you hate so much):
decode( expr
, value_to_match, result_if_matched
, value_to_match2, result_if_matched2
, result_if_no_match
)

Of course, I should probably be using CASE more, but old habits, etc.

# posted by

jpiwowar : March 29, 2011 at 2:27 AM

@gary

I spent a month or 2 with 8i and I did learn how to use DECODE (my boss was delighted!). But CASE came around about that time, 8i or 9i, and I naturally gravitated toward that.

All you "old" people scare me...I don't know how you learned anything before the internet. I'm in constant amazement.

# posted by

oraclenerd : March 30, 2011 at 1:22 AM

@tim

Yeah, see my previous comment. I started in 2002. Career change. I consider myself fortunate.

I do remember printing out docs, all 6 or 700 pages. Yikes. I think the dedication level was much higher without The Google Machine, had to be.

# posted by

oraclenerd : March 30, 2011 at 1:23 AM

@stew

Funny, I was reading an old AskTom post and saw your name in there.

That's something I did not know and another reason I love the tubes, the collective knowledge is greater than a single person's, except maybe Tom's. :)

# posted by

oraclenerd : March 30, 2011 at 1:25 AM

@jp

i'll never break you of that habit, but that's ok, because i know you'll be consistent(ly bad) at putting it in front. :p

# posted by

oraclenerd : March 30, 2011 at 1:28 AM

I don't know how you learned anything before the internet.

It was simple, we'd read the manual and follow the directions. Then we'd show the dummys who went to CDC computer school and spent all their time on Plato how to do actual work.

# posted by

Joel Garry : March 30, 2011 at 8:29 PM

@joel,

manual? directions? what, pray-tell, are those?

# posted by

oraclenerd : March 30, 2011 at 8:31 PM

I recall offices that had a shelves full of manuals. Not necessarily the right ones, mind you. Updated ones were expensive ! They didn't come free.

The real reason for bloatware is that they don't have to print the manuals any more. You think there would be so many system views and parameters if they had to deal with 1200 pages of the the Database Reference plus another 1000 of the Admin Guide.

There are over five thousand pages in the PL/SQL Packages and Types Reference manual. You have to go through over a hundred pages just to get to the first chapter. I'm convinced that some where in there is an Easter Egg message for DBMS_SUBTRANSACTION.APRIL_FOOL

# posted by

Gary Myers : March 31, 2011 at 12:49 AM

@gary

There has to be some sort of Easter Egg in there, has to.

5K pages? I did not know that. I haven't used the pdf version in some time though, does anyone still use those?

# posted by

oraclenerd : March 31, 2011 at 12:59 AM

A single PDF is a bit easier to search than a collection of HTML pages.
Well obviously not to Google, but when the HTML isn't on the web.

And my workplace has the download.oracle.com locked off by the web proxy during work hours to reduce internet usage. Go figure.

# posted by

Gary Myers : March 31, 2011 at 6:33 PM