[thelist] RE: SQL Case/Decode
Luther, Ron
ron.luther at hp.com
Tue Jul 27 12:59:39 CDT 2004
Joshua Olson kindly suggested:
>>Sounds complicated.
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.]
{I did remember the older app I built that lets users cross the
year-end boundary and pull up to 12 months of data - but that
one uses JS and, because of the technology I'm using on this one
I'll have to do it all in SQL.}
>>You may want to consider a view for this type of problem:
[Snipped some much appreciated 'view magic' -- I have saved that
off to peruse later. Thanks Joshua!]
Okay. I'll admit it. I usually run kicking and screaming from
"views". I've never had good luck with them. My guess is that
I may have mostly run into poor applications for them. Normally,
(when *I* come across them anyway), I'm dealt read access to a
'view' that selects a subset of records from some horrendously
large file. Then, as I link in the 8 or 10 additional tables
I need to construct my report ... performance goes to hell in
a handbasket. ;-(
However, that *is* an interesting approach. One I wasn't considering.
Actually, for this application, I think it would be a little
simpler ... just have the view do the magic to pull off the last
available completed month of data ... select "col1" from the view!
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!) ;-)
The idea was to do this 'quick and dirty' without pestering those
busy IT folks! 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!!
{In my infamous 'spare time' I'll see about downloading a recent
copy of Oracle ... I need to play with those view and trigger
things anyway!}
Thanks,
RonL.
[1] Over the last <mumble-mumble> years I've cut code in quite a
few languages ... Fortran, Cobol, MarkIV, Snobal, Forth, Fifth,
Action, Pascal, Basic, Assembler, Perl, PHP, ASP, Cold Fusion,
etc ... but I've never actually *worked* in IT. I just kinda
get stuff done.
More information about the thelist
mailing list