SQL Case/Decode --> wuz RE: [thelist] ASP and PHP Correlation
Luther, Ron
ron.luther at hp.com
Tue Jul 27 10:22:15 CDT 2004
Joshua Olson suggested:
>>Sounds like a good time for Case...When. Example (In MSSQL Syntax)
>>SELECT thevalue =
>> CASE
>> WHEN DatePart(m, GetDate()) = 1 THEN col1
>> WHEN DatePart(m, GetDate()) = 2 THEN col2
>> END
>>FROM thetable
Thanks Joshua,
;-)
[I really didn't mean to hijaak the other thread. Honest.]
It's an Oracle db. I think 'decode' may be their version of 'case'.
That looks kinda similar to what I was thinking with the decode:
<psuedo-code only>
DECODE(MONTH(SYSDATE), 1, table.col1, 2, table.col2, etc.... 'else
clause goes at the end')
<pseudo-code />
The fun part comes into play, naturally, at year-end boundaries.
[Architecture in play ... the 'current year' table has 12 monthly
columns that get populated as the year trundles along. At the end
of the year the 'completed' table gets archived off into a history
table. The history table has a similar structure - but with an added
date field to note what year the individual data record applies to.
e.g. 12-31-2003 or 12-31-2004, etc.]
That means I have to link the current year table to the history
table. (And be careful with the outer join direction to make sure
I don't suppress any data I really want the user to see.) Then I have
to modify the decode to pull say 'December' out of the history table
if the user is running the report in 'January'. Then finish that off
with an added 'where clause' to pick off the correct prior year. Fun!
{That where clause is, IIRC, an ugly beast in Oracle.)
I may look around a bit ... I've probably written this before.
RonL.
A 'boundary case' if there ever was one! ;-)
More information about the thelist
mailing list