Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Interesting report problem 8i

Interesting report problem 8i

From: GW <gwandsh_at_KILLpacifierSPAM.com>
Date: Fri, 21 Mar 2003 18:49:38 -0800
Message-ID: <v7njgtp5lg1k7e@corp.supernews.com>


Folks, I am looking to see if anyone has a suggestion to approach this problem :

I have a set of tables that I will process with cursors to extract raw data for a reporting process. I will likely grab this data and add it to a PL/SQL table to massage it further. In other words, I will have the data available to me in "vertical" format.

Based on a date value included within this table, the data may apply to one of N output columns in my report format. Each of N columns represents data for a month, and for now, N will be 6 (months). The trick is that I have to cross-check between *each* N and N+1 to see if there is a change in certain data elements before the entire report "row" gets included. If there are no changes in the elements between all months, I am not insterested in the row. In the example below, the Blah row qualifies because tow adjacent months contained a difference. The More blah row also qualifies because one month changes in a future month.



Static data Month N-6 Month N-5 Month N-4 .... Month N
Blah blah             A,B,C             A,X,Y
More blah           R,V,S

C,F,D

Essentially I am trying to take a vertical store of data and turn it horizontal. Usually I approach this with dynamic views (i.e. one (select a,b,c from table) statement per report column), and then select my final set of data from the various views. In this case the number of columns will likely be variable. Plus I would have to use outer joins between each dynamic view - the processing costs would be prohibitive.

I could hard-code (uhg) a table structure with N (6) months and load it up programatically, but I would have to recode things every time the user decided on a new date range. I also am limited in bringing back extra rows and massaging it in the presentation layer, as the user needs to be able to save off this data in a spreadsheet (Yes, they still live by spreadsheets). So basically the return set must be in "final" format.

Anybody been down a similar road before ? If so and you have suggestions, I'd sure love to hear them. Oh, yes, there is also a critical time factor involved. What else is new ? ;-)

Cheers
Gary Wood
gwood_at_eli.net Received on Fri Mar 21 2003 - 20:49:38 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US