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