Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Interesting report problem 8i
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.
Blah blah A,B,C A,X,Y More blah R,V,S
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