[thelist] RE: SQL Case/Decode

Joshua Olson joshua at waetech.com
Tue Jul 27 11:14:53 CDT 2004


> -----Original Message-----
> From: Luther, Ron
> Sent: Tuesday, July 27, 2004 11:22 AM
>
> 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.

Ron,

Sounds complicated.  You may want to consider a view for this type of
problem:

CREATE VIEW mucheasiertoworkwith AS
SELECT DatePart(y, thedate) AS year
  , 1 AS month
  , field1 AS value
FROM history
UNION
SELECT DatePart(y, thedate) AS year
  , 2 AS month
  , field2 AS value
FROM history
UNION
SELECT DatePart(y, thedate) AS year
  , 3 AS month
  , field3 AS value
FROM history
... etc for the rest of the months ...
UNION
SELECT 2004 AS year
  , 1 AS month
  , field1 AS value
FROM thetable
UNION
SELECT 2004 AS year
  , 2 AS month
  , field2 AS value
FROM thetable
UNION
.. etc for the rest of the months ...

Then, select from the view instead of the underlying tables using the year
and month fields as filters.

HTH, (and hoping you wanted help)

<><><><><><><><><><>
Joshua Olson
Web Application Engineer
WAE Tech Inc.
http://www.waetech.com/service_areas/
706.210.0168




More information about the thelist mailing list