Basket Case – CASE statement in SQL

Following on from my previous post, I’m rapidly reaching the conclusion that the CASE statement is, well, a little bit touchy.
First off, here’s a quick recap or you can have a look here.:

create table final_four ( 
  team varchar2(30) NOT NULL, 
  winners VARCHAR2(30), 
  runner_up VARCHAR2(30), 
  third VARCHAR2(20), 
  fourth VARCHAR2(20), 
  best VARCHAR2(30)) 
/ 

insert into final_four( team, winners, runner_up, third, fourth, best) 
values( 'URUGUAY', '1930, 1950', null, null, '1970', 'WINNER'); 

insert into final_four( team, winners, runner_up, third, fourth, best) 
values( 'GERMANY', '1954, 1974, 1990', '1966, 1982, 1986, 2002', '1970, 2006', '1958', 'WINNER'); 

insert into final_four( team, winners, runner_up, third, fourth, best) 
values( 'HOLLAND', null, '1974,1978', '1998', NULL, 'RU'); 

insert into final_four( team, winners, runner_up, third, fourth, best) 
values( 'SPAIN', null, null, null, '1950', '4TH');

In demonstrating how COALESCE was the most compact and simple function to use I wanted to contrast it with, among others, the CASE statement. However, when I ran this :

SELECT team, best, 
    CASE winners WHEN NULL THEN 
            CASE runner_up WHEN NULL THEN 
            CASE third WHEN NULL THEN fourth ELSE third END 
        ELSE runner_up END 
    ELSE winners END years 
FROM final_four 
/

The result was

TEAM		BEST	   YEARS 
--------------- ---------- ------------------------------ 
URUGUAY 	WINNER	   1930, 1950 
GERMANY 	WINNER	   1954, 1974, 1990 
HOLLAND 	RU 
SPAIN		4TH 

Fortunately, Martin came to the rescue. His suggestion was :

SELECT team, best, 
    CASE NVL(winners, '0') 
        WHEN '0' THEN 
            CASE NVL(runner_up, '0') 
                WHEN '0' THEN 
                    CASE NVL(third,'0') 
                        WHEN '0' THEN fourth 
                    ELSE third 
                    END 
                ELSE runner_up 
                END 
            ELSE winners 
        END years 
FROM final_four 
/

Note that he’s using the character ’0′ rather than the number 0 as the default value in the NVL function. I missed this particular nuance first time and got :

ORA-00932: inconsistent datatypes: expected CHAR got NUMBER.

Which, in itself, is slightly odd.
I had been working on a different and – frankly- less elegant solution :

SELECT team, best, 
    CASE NVL( LENGTH( winners), 0 ) 
        WHEN 0 THEN 
            CASE NVL( LENGTH( runner_up),0) 
                WHEN 0 THEN 
                    CASE NVL( LENGTH( third), 0) 
                        WHEN 0 THEN fourth 
                        ELSE third 
                    END 
                ELSE runner_up 
            END 
        ELSE winners 
    END as years 
FROM final_four 
/

This also works…which is strange at first sight because I’m mixing numeric expressions and VARCHAR return values, so it should give me the same error.
Oh well, when all else fails, read the manual.
The Oracle documentation for SQL CASE states that, for a simple comparison such as this, the datatype returned from the WHEN expression needs to match that of the CASE expression. So, whilst Oracle will be perfectly happy when you do this :

SELECT NVL(winners, 0) FROM dual;

it’ll get all sulky and uncooperative when you drop it into a CASE statement.

All of which leads me back to where this all started – namely, if you want to return the first not null value in a list of three or more elements, COALESCE offers the simplest way to go.
Thus ends the curious case of CASE.
Update – the final answer to the CASE conundrum is here.

About these ads

3 thoughts on “Basket Case – CASE statement in SQL

  1. Your problem is related to the difference between ‘if x = null’ and ‘if x is null’. When you say ‘case x when null’ you doing an equality check which isn’t giving you the result you want. Try ‘case when x is null’ and you should get the desired result.

    Glen

    • Glenn,

      I think you may have a point. However when you try the syntax you suggested :
      SQL> l
      1 SELECT team, best,
      2 CASE winners WHEN winners IS NULL THEN
      3 CASE runner_up WHEN runner_up IS NULL THEN
      4 CASE third WHEN third IS NULL THEN fourth ELSE third END
      5 ELSE runner_up END
      6 ELSE winners END years
      7* FROM final_four
      SQL> /
      CASE winners WHEN winners IS NULL THEN
      *
      ERROR at line 2:
      ORA-00905: missing keyword

      This is the same when you reverse the logic ( i.e. WHEN winners IS NOT NULL).
      This example is in SQL on Oracle 10g XE ( I also tried the stuff in the original post on 11gR2).

      Not sure if you’re example works on another RDBMS, but unfortunately, Oracle’s having none of it.

  2. Sorry, I should have included an example :

    select case when dummy is null then ‘*null*’ else ‘in else’ end
    from dual;

    For completeness :

    case var when val_1 then ‘x’ else ‘y’ end

    is the same as

    case when var = val_1 then ‘x’ else ‘y’ end

    whereas

    case var when null then ‘x’ else ‘y’ end

    is the same as

    case when var = null then ‘x’ else ‘y’ end

    which is different to what you want

    case when var is null then ‘x’ else ‘y’ end

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s