Thursday, May 03, 2007

One liner


I was untangling a query when I came across eight variations of this code where only the literals 'a' to 'e' changed. Two of these were nested within an additional NVL so that the second would execute if the first returned null.
nvl(decode(2, 1, 'a', 2, 'b', 3, 'c', 4, 'd', 5, 'e', ' '), 'na')

8 comments:

Anonymous said...

Sorry, where is the WTF?

Anonymous said...

What about using the null in the decode? He/she needed just one function, not two :-)

Tony Andrews said...

Anonymous, you need to look more carefully:

decode(2,...

This code takes the constant number 2 and then determines whether it is a 1, 2, 3, 4, 5 or other value. Last time I checked, a 2 was always a 2...

Anonymous said...

Ooooops, right! :-)

Dan said...

Even if sometimes a 2 isn't a 2, the NVL in this code is pointless. The default value of ' ' at the end of the decode guarantees that the decode will never return a null.

3360 said...

Yes I think the nvl is intended as a distraction from the uselessness of the decode. This variation was particularly cunning.

nvl(
nvl(decode(2, 1, 'f', 2, 'g', 3, 'h', 4, 'i', 5, 'j', ' '), 'na'),
nvl(decode(2, 1, 'k', 2, 'l', 3, 'm', 4, 'n', 5, 'o', ' '), 'na')
)

Doug Burns said...

Hooray! Welcome back ;-)

Damn Sploggers :-(

Kieran said...

That looks like it was machine-generated by something else, to me, and then pushed into Oracle...

But then why not just work out the decode in the other language? Mysterious.