Definitely the final post about CASE – things that make you go Doh !

I’d like to start this large slice of humble pie by saying a big thanks to Glenn and Martin who both pointed out – ever so politely – that I’ve been a bit thick.
Where I went wrong was in applying CASE as a direct synonym for the venerable DECODE.
To appropriate Glenn’s example

SELECT DECODE( dummy, NULL, 'Oh dear', 'All OK') FROM dual
/

works fine. However, when applying the same syntax to CASE, you hit problems.

The correct solution is :

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

As you can see – it is possible to specify a conditional comparison after the WHEN clause.
On the plus side, I do finally have a working example to emphasise the point of my original post – i.e. that using COALESCE is the cleanest way to select the first NOT NULL value from a list

SELECT team, best, COALESCE( winners, runner_up, third, fourth) as years
FROM final_four
/

Behind which fig-leaf I will now, in time-honoured fashion….take hand, apply sharply to forehead, utter oath.

About these ads

Tags: sql case