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