That Reminds Me...

Sometimes the solutions people come up with to a given problem are breathtaking in their combination of ingenuity and insanity. Take this view, which exists purely to transform a boring "reminder number" like 3, 4, 5, ... into the English text "Third Reminder", "Fourth Reminder", "Fifth Reminder", ...

CREATE OR REPLACE VIEW reminders_view AS
SELECT reminder_id
, reminder_seq
, ( SELECT 
      DECODE (reminder_seq, 
       1, 'No reminder', 
       2, 'Reminded', 
       3, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
       4, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
       5, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    6, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    7, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    8, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    9, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    10, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    11, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    12, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    13, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    14, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    15, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    16, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    17, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    18, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    19, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    20, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    21, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    22, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    23, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    24, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    25, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    26, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    27, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    28, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    29, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    30, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
       'Reminded') FROM DUAL) reminder_type 
FROM reminders;

What a beauty! It works as follows:

1) Use a DECODE to determine what the number is. Note that 2 is treated as a special case, even though it ends up the same as all the "other" numbers above 30.

2) For numbers between 3 and 30:

2.1) Convert to a string

2.2) Concatenate with '-MAY-2004'

2.3) Convert to a date

2.4) Convert back to a string using the 'ddspth' format mask

2.5) Convert to Init Caps

2.6) Append the word ' Reminder'

3) Enclose the whole lot in a redundant "(SELECT ... FROM DUAL)" scalar subquery.

Obviously, this saved the developer from the tiresome task of typing 'Third', 'Fourth', 'Fifth' etc. And presumably he/she isn't aware of the CASE expression that could have reduced it to:

CREATE OR REPLACE VIEW reminders_view AS
SELECT reminder_id
, reminder_seq
, CASE WHEN reminder_seq = 1
           THEN 'No reminder'
       WHEN reminder_seq BETWEEN 3 AND 30
           THEN INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder'
       ELSE 'Reminded'
       END reminder_type 
FROM reminders;

(And why stop at 30 when May has 31 days?!)

Well, it gave me a much needed laugh on a Friday afternoon, anyway.

All names have been changed to protect the guilty.

Why Machines Will Never Take Over The World

It is a recurring theme in science fiction that as computers increase in power and sophistication, they may one day reach a point where they decide they can do better without us, and condemn us to lives suspended in racked pods with our brains plugged into a vast virtual world, or alternatively send increasingly resourceful and indestructable robots to hunt us down in our bunkers, while above ground survivors fight a desperate war for survival amidst the wreckage of civilisation.

If you find this vision of the future alarming, take heart in this SQL query, which was generated by a machine. Not, perhaps, a Cyberdyne Systems T-800 or the Matrix Mainframe, but something called OLAP API. We don't know what that is either, but we can tell you that if any robot descended from it ever attempts to enslave humanity using a virtual world and an unfeasible pod system, rest assured that you will have time to get out of its way.