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.