[thelist] RE: SQL Case/Decode

[Luther, Ron]

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.