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 runner_up IS NULL THEN
            CASE WHEN third IS NULL THEN fourth
                ELSE third
            ELSE runner_up
        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.

Leave a Reply

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

You are commenting using your 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