I could design a function with a cursor to return the requested data below but would like to know if you can recommend a better design method such as an efficient Oracle query or use of Analytics??
We have a large Oracle 10g table, Tbl_Events, that keeps track of the scheduled date, completed date, and cost for each process (event) that is scheduled or completed on our “Files”.
Not every one of our “Files” requires the same processes (events).
An example of data in this table, where two “Files” are being processed (1001 and 1002) and the associated events' completed and scheudled dates and costs for each of these “Files”:
File Event Sched_Date Compl_Date Cost_Amt
1001 A1 10/01/2006 10/02/2006 20.10
1001 A2 10/03/2006 40.00
1001 B1 10/01/2006
1002 A1 09/01/2006 50.00
1002 B2 09/15/2006 09/14/2006 35.50
1002 B3 10/02/2006
The "query" will need return values for specific events for all "Files" in a single row per “File”.
For example, if I only want the A1 and B3 Events for the “Files” in the table above I would want to design a query to return the following rows (one for each “File”) and columns:
File A1_Sched A1_Compl A1_Amt B3_Sched B3_Compl B3_amt
1001 10/01/2006 10/02/2006 20.10 NULL NULL NULL
1002 09/15/2006 NULL 50.00 10/2/2006 NULL NULL
I assume that I will need to hard code the desired Events and resulting column names.
Thanks in advance,