Thursday, September 11, 2014

Why use CASE when NVL will do?

I've found that many developers are reluctant to use "new" features like CASE expressions and ANSI joins. (By new I mean: this millennium.)

But now they have started to and they get carried away.  I have seen this several times recently:

    CASE WHEN column1 IS NOT NULL THEN column1 ELSE column2 END

Before they learned to use CASE I'm sure they would have written the much simpler:

    NVL (column1, column2)

Now I now that NVL is Oracle-specific and CASE is portable, but (a) we aren't ever going to be porting our millions of lines of PL/SQL, and (b) I can guarantee they didn't do it for that reason. They have got a new hammer and now everything looks like a nail.

3 comments:

SSentinel said...

Then there's the case where you see:

CASE WHEN column1 IS NOT NULL THEN expression1 ELSE expression2 END

used instead of NVL2:

NVL2(column1,expression1,expression2)

However, for the most part I think that the case statement is a good replacement for the decode function.

Tony Andrews said...

Confession: I have never used NVL2, not once!

Scott Wesley said...

I have a good reason - short circuit evaluation. In certain scenarios there can be a performance gain to be had out of using CASE/COALESCE.

I've talked about this here
http://www.grassroots-oracle.com/2010/02/short-circuit-evaluations-moving-away.html