The CASE against DECODE and the Misery of Penalties

Euro2012 has come and gone. That sigh of relief is the sound of Deb reclaiming the TV remote and banning me from watching any more sport for the rest of the summer.
Spain have confirmed themselves as one of the great teams in history by winning yet again.
It has been said that they are boring. As far as I can see, the only boring thing about them is their predictability in not letting anyone else win.
By that measure, England are pretty boring as well although, if you wanted to be a bit more positive, you’d say consistent. How much of a lottery can penalties be when you lose all the time ?
I’m not even going to pretend that the above ramble connects in any way to the subject of today’s wander through the wacky world of ANSI SQL…although you may notice that I’ve taken inspiration from recent events for some of the examples.
When Oracle first came out, there was no ANSI standard SQL. There weren’t any other relational database on the market.
As a result, there are various programming constructs that are still a bit non-standard.
Yes, Oracle has introduced the ANSI standard equivalents over time and insisted that both the proprietary and ANSI syntax work in exactly the same way. This is true. For the most part.
I have already noted the advantage of ANSI join syntax when using more than one outer join.
Here, however, I’m going to turn my attention to a useful little feature of the CASE statement. Continue reading


More Nefarious Nullable Nonsense – Coalesce

OK so it’s not really nefarious but I couldn’t think of another word beginning with “N”.
You may also be relieved to know that this is the dying parp of the vuvuzela on this blog.

This is really just a bit about coalesce – a little used but extremely useful SQL function.

Let’s get the World Cup example out of the way. Here’s a table containing details of the four semi-finalists from this year’s tournament.

Conveniently enough, they consist of two previous winners, one with a best performance as beaten-finalists, and one with a previous best of 4th. Too good an opportunity to miss…. Continue reading