[thelist] RE: SQL Case/Decode

[Joshua Olson]

> -----Original Message-----
> From: Luther, Ron
> Sent: Tuesday, July 27, 2004 2:00 PM
>
> I guess anytime you try to 'repurpose' data constructed for one
> application into another you can run into some interesting
> challenges.  [I've had some nice chats with Rudy about the
> trade-offs of building ten purpose built tables with simple
> 'read and display' reports versus building one general purpose
> table and ten tricker reports. It gets interesting when you
> start considering the downstream maintenance/modification work!
> I probably lean a little too much to the 'tricky report' side.
> My IT team probably leans too much to the 'read and display'
> side. Truth is most likely somewhere in the middle.]

It's not uncommon to represent data two ways--one for each of the different
applications of the data.  One application of the data is reporting, and the
other is transactions.  Ironically, the two applications for the data are
commonly referred to as OLAP (Online Analytical Processing) [0] and OLTP
(Online Transaction Processing) [1].

I've seen some massive systems built for publishing data from the OLTP
system to the OLAP system for reporting.  Usually, I see this in batch
processes.  At some point the database is backed up, the data is then
shuffled off to a process that converts, translates, and reorders the data.
Then, reports can be run easily against the new form of the data.

Truth is, like you said, in the middle somewhere.  But not as a compromise
of the two, but as the aggregate of the two, in most cases.

> Unfortunately <here comes the really sad part /> I don't have dba
> access. I'm not even in IT - never have been. [1] (So you should
> hear the screams when I ask for 'resource' level rights!) ;-)

Oh my.  What a shame.  Is there any way that you can at least pull the data
into something you CAN control for reporting purposes?

> The idea was to do this 'quick and dirty' without pestering those
> busy IT folks!

I've been in IT.  They are not usually as busy as you sarcastically allude
to... but it sounds like you know that.

> However, any solution involving a 'view' would
> require their awareness and participation ... so that's kinda a
> "last resort" for me at the moment. But _I will_ certainly keep
> it in mind for future projects!!

Views are basically the same thing as a derived table.  So, let's say that
you have a view..

CREATE myview AS
SELECT blah
FROM foo
WHERE bar = 123

You use this in a query as such:

SELECT *
FROM myview

But, you can ALSO do this, without the view:

SELECT derived.*
FROM
(
  SELECT blah
  FROM foo
  WHERE bar = 123
) derived

Just put your code for the view in between the ()'s and call it something
for reference (I've called it derived, for sake of simplicity).

Morale of the story is that you don't have to have access to views to apply
work done in a view to a "regular" query.

[0] http://en.wikipedia.org/wiki/OLAP
[1] http://en.wikipedia.org/wiki/OLTP

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