I have to write a difficult piece of SQL, to return a dataset to be displayed in a grid. It's using Delphi 2006 on Oracle 9.2? tables.
The source table of data ONL_DATA looks like this:
Sample data looks like this:
CustomerID Account Datastream Data_Quality Data_Priority Data_Interval
1 ABC123 A1 D 1 14-07-2009 20:15
1 ABC123 A1 A 2 14-07-2009 20:15
1 ABC123 B1 D 1 14-07-2009 20:15
1 ABC123 B1 C 2 14-07-2009 20:15
1 ABC123 A1 D 1 14-07-2009 20:30
1 ABC123 A1 B 2 14-07-2009 20:30
1 ABC123 B1 D 1 14-07-2009 20:30
1 ABC123 B1 A 2 14-07-2009 20:30
1 XYX123 A1 D 1 14-07-2009 20:15
1 XYX123 A1 D 1 14-07-2009 20:30
1 XYX123 A1 A 2 14-07-2009 20:30
1 XYX123 B1 D 1 14-07-2009 20:30
1 XYX123 B1 C 2 14-07-2009 20:30
The requirement is for the SQL statement when executed with a single given date, and a given CustomerID passed in, to return for each unique instance of Customer and Datastream, a line showing:
* "20:15": the Data_Quality of the latest (i.e. highest numbered) Data_Priority record for the 20:15 Data_Interval for this Account and Datastream
* "20:30": the Data_Quality of the latest (i.e. highest numbered) Data_Priority record for the 20:30 Data_Interval for this Account and Datastream
So the required returned dataset for the above data should be:
Account Datastream "20:15" "20:30"
ABC123 A1 A B
ABC123 B1 C A
XYZ123 A1 D A
XYZ123 B1 C
Note the null in "20:15" for XYZ123/B1.
Data_Priority is always unique for a given CustomerID, Account, Datastream and Data_Interval.
In the final version there will be obviously be 96 time interval data quality fields covering a whole day; I have simplified the example.
If at all possible, this needs to be a single (long) SQL statement. This is being run in Delphi in an app where every other grid of data is generated by simply applying an SQL-result dataset to the grid, so if I have to somehow generate it line by line, that would be a distinct disadvantage.
The required SQL is obviously going to use a series (96 of them) of subqueries. Ideally I'd like to keep these as short as possible because once I have one written, I write it as a single line in Excel, concatenating in the timeslot, so I can generate all 96 and then copy them into Delphi. Cumbersome but best I can come up with.
My false starts:
A) I've tried using constructions like:
select data_quality from ONL_DATA od2
where od2.Account = od1.Account
and od2.datastream = od1.datastream
and data_interval = to_date('14-07-2009 20:15','dd-mm-yyyy HH24:MI')
and ROWNUM = 1
ORDER BY od2.data_priority desc
... as the subquery (where od1 is the name of ONL_DATA in the main SQL statement) but while this subquery works when run by itself (with the od1.Account and od1.datastream replaced by hardcoded values), it doesn't seem to work as a subquery with od1 stuff passed in.
B) I've also tried more basic subquery constructions like:
(select data_quality from onl_data u
where data_interval = to_date('14-07-2009 20:15','dd-mm-yyyy HH24:MI')
and u.Account = od1.Account
and u.datastream = od1.datastream
and u.data_priority =
(select max(data_priority) from onl_data t
where t.data_interval = to_date('14-07-2009 20:15','dd-mm-yyyy HH24:MI')
and t.Account = u.Account
and t.datastream = u.datastream)) as "20:15"
FROM ONL_DATA od1
WHERE customerID = 2
GROUP BY od1.Account, od1.datastream
This SQL actually executes but returns null values.
The concept of using 96 subqueries does work. Initially this SQL did not have to find the highest data_priority line: there was only one line per Account/datastream/timeslot. I had it working then. But this one is defeating me. Is this just too complex to do in the way I am trying to do it?