Friday, June 23, 2006

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.

2 comments:

Herod T said...

This is a frightening thought.

May is only one month. Did they have a view like this for every month of the year????

Ditto said...

Of course, one more step makes it so much more simpler ... and unrestricted to "30" or even "31" ... ;)

SELECT reminder_id
, reminder_seq
, CASE WHEN reminder_seq = 1
THEN 'No reminder'
WHEN reminder_seq BETWEEN 3 AND 30
THEN TO_CHAR(TO_DATE(reminder_seq,'J'),'Jspth')
ELSE 'Reminded'
END reminder_type
FROM reminders

(PS: Credit for this isn't mine, I learned this from asktom.oracle.com)
:)

.. but it is scary what some people think of sometimes .. :o