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.

CASE and DECODE

Whilst the question of ANSI syntax can arouse great enthusiasm or antipathy among Oracle Programmers, I have to say that I’m not bothered either way. My attitude is more toward the side of sticking with what you know unless there’s a really good reason to change.
One exception to this is the DECODE statement.
DECODE is Oracle’s take on the SWITCH/CASE statement which is a staple of many 3GLs. Having cut my programming teeth in Pascal and C, coming across DECODE was a bit of a wrench. So, when CASE came along, I happily used it as a preference.
Sure, you still see DECODE in older code ( or code written by older programmers) but I happen to prefer the clarity of a CASE statement.
Either way, this is just personal preference on my part…most of the time.
Anyway, time for an illustration of the syntax of these two constructs. First of all, lets knock up some test data :

CREATE TABLE shootouts(
    team_name VARCHAR2(30),
    no_shootouts NUMBER(2),
    no_wins NUMBER(2))
/

INSERT INTO shootouts( 
    team_name, no_shootouts, no_wins)
VALUES(
    'ENGLAND', 7, 1)
/

INSERT INTO shootouts( 
    team_name, no_shootouts, no_wins)
VALUES(
    'GERMANY', 6, 5)
/

CREATE TABLE chances(
    team_name VARCHAR2(30),
    back_yn VARCHAR2(1))
/

INSERT INTO chances(
    team_name, back_yn)
VALUES(
    'ENGLAND', 'N')
/

INSERT INTO chances(
    team_name, back_yn)
VALUES(
    'GERMANY', 'Y')
/

COMMIT;

It’s a prototype for Auto Pundit – a new application to randomly generate anodyne statements at half-time in big football matches. Trust me, in TV studios everywhere, ex-footballers are quaking in their boots.
Oh, and yes, the figures are right, Germany have actually lost a penalty shootout. If you’re too young to remember Czechoslovakia’s triumph in the final of Euro 76, have a look at Antonin Panenka carving his place in football history during that very same shootout.
Anyway, let’s see if we can generate a suitable comment on the prospects for either of these teams in the event of penalties.
Using DECODE :

SELECT s.team_name,
    DECODE( c.back_yn, 
        'Y', 'Gotta fancy them in this situation', 
        'Years of hurt ++') as "Hansen says"
FROM shootouts s, chances c
WHERE s.team_name = c.team_name
/

TEAM_NAME		       Hansen says
------------------------------ ----------------------------------
ENGLAND 		       Years of hurt ++
GERMANY 		       Gotta fancy them in this situation

SQL> 

I know Alan Hansen probably wouldn’t use auto-increment syntax to have a dig at the English, but this is a prototype.
And now exactly the same thing with CASE :

SELECT s.team_name,
    CASE c.back_yn
        WHEN 'Y' THEN 'Gotta fancy them in this situation'
        ELSE 'Years of hurt ++'
    END as "Hansen says"
FROM shootouts s, chances c
WHERE s.team_name = c.team_name
/
TEAM_NAME		       Hansen says
------------------------------ ----------------------------------
ENGLAND 		       Years of hurt ++
GERMANY 		       Gotta fancy them in this situation

SQL> 

The sub-query

Imagine, if you will that, as a result of the trauma caused by being caught in the middle of too many heated discussions on the relative merits of ANSI join syntax, I’ve developed a rare allergy to the humble equi-join.
As a result, I have developed Sub-query Compulsion Disorder so need to write our query as follows :

SELECT s.team_name,
DECODE(
    SELECT c.back_yn
    FROM chances c
    WHERE c.team_name = s.team_name,
    'Y', 'Looking good for them',
    'Years of hurt ++') as "Hansen says"
FROM shootouts s
/

    SELECT c.back_yn
    *
ERROR at line 3:
ORA-00936: missing expression

SQL> 

Oh. Oracle seems to be oblivious to my plight and refuses to run the query. What now ? I mean, it’ll be exactly the same if I use CASE, won’t it ?

SELECT s.team_name,
    CASE (
        SELECT back_yn
        FROM chances c
        WHERE c.team_name = s.team_name)
        WHEN 'Y' THEN 'Gotta fancy them in this situation'
        ELSE 'Years of hurt ++'
    END as "Hansen says"
FROM shootouts s
/

TEAM_NAME		       Hansen says
------------------------------ ----------------------------------
ENGLAND 		       Years of hurt ++
GERMANY 		       Gotta fancy them in this situation

SQL> 

NOTE it was at this point that Jeff Kemp pointed out a little known side-effect of this condition – Bracketitus. If you actually run the following statement ( with the sub-query in brackets) it will work ( Thanks Dr Jeff !)

SELECT s.team_name,
DECODE(
    (
    SELECT c.back_yn
    FROM chances c
    WHERE c.team_name = s.team_name
    ),
    'Y', 'Looking good for them',
    'Years of hurt ++') as "Hansen says"
FROM shootouts s
/

So, not really that different after all…unless you miss out the brackets. The trauma of England’s gallant loss to Italy must have taken more of a toll than I thought.

I’m hoping that Deb will relent and my sport-watching ban will be lifted in time for the Olympics. Interestingly, the Great Britain football squad has recently been named. Of the 18 players named, 13 are English…and five are Welsh. If they happen to find themselves in a penalty-shootout, can you guess who the five penalty takers are going to be ?