Oracle – for when it was like that when you got there

Main menu

Post navigation

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