|
Re: Complex algorithm for generating a BIRT table [message #657101 is a reply to message #657061] |
Tue, 01 March 2011 15:35 |
|
Where is that dataset coming from? Whatever is generating that dataset ought to be modified to generate the dataset that meets your table needs.
I assume that the data is supposed to be grouped by devicename.
What you have in your requirement is basically a row-over-row comparison. You can do all of that logic within BIRT in the fetch (I think) event of the dataset. If you try to push this to the back end that currently provides your data, you'll find that you have to use a stored procedure or something that allows for SQL cursor operations - your transform is not something that maps to normal SQL set-based logic.
I would think that you can take the code from the java bean and translate it - but I don't have bandwidth right now to try a proof of concept.
|
|
|
Re: Complex algorithm for generating a BIRT table [message #657148 is a reply to message #657101] |
Tue, 01 March 2011 18:06 |
Mircea S Messages: 2 Registered: March 2011 |
Junior Member |
|
|
Thanks for the reply. Unfortunately I am not allowed to add stored procedures to the database. However I did write an sql query (over a period of several days) which does all the logic. I am not proud of the monster I created though:
SELECT TABLE1.DeviceName, StartTime, Status, Duration, MaintenanceInfo, TABLE2.action_name, table2.time FROM
( SELECT ad.device_name AS DeviceName,
COALESCE((SELECT ada1.time FROM acc_device_action as ada1 JOIN acc_device AS ad1 ON ada1.acc_device_id = ad1.id WHERE ada1.action_name = 'MAINTENANCE_START' AND ada1.time < ada.time AND ad1.device_name = ad.device_name AND ada1.TIME > '2011-02-21 11:40:31' AND ada1.TIME < '2011-02-22 23:59' ORDER BY (ada.time-ada1.time) limit 1), '2011-02-21 11:40:31') AS StartTime,
CASE WHEN COALESCE((SELECT max(ada1.time) FROM acc_device_action AS ada1 JOIN acc_device AS ad1 ON ada1.acc_device_id = ad1.id WHERE ada1.action_name = 'MAINTENANCE_START' AND ada1.time < ada.time AND ad1.device_name = ad.device_name AND ada1.TIME > '2011-02-21 11:40:31' AND ada1.TIME < '2011-02-22 23:59'), '2011-02-21 11:40:31')
<= COALESCE((SELECT max(ada1.time) FROM acc_device_action AS ada1 JOIN acc_device AS ad1 ON ada1.acc_device_id = ad1.id WHERE (ada1.action_name = 'DEVICE_DEFECT' OR ada1.action_name = 'DEVICE_REPAIRED') AND ada1.time < ada.time AND ad1.device_name = ad.device_name AND ada1.TIME > '2011-02-21 11:40:31' AND ada1.TIME < '2011-02-22 23:59'), '2011-02-21 11:40:31'::timestamp-'00:01'::interval)
THEN
'Device NOT Available'
ELSE
'Device Available'
END AS Status,
(SELECT COALESCE(min(ada.time - ada1.time), ada.time - '2011-02-21 11:40:31') FROM acc_device_action AS ada1 JOIN acc_device AS ad1 ON ada1.acc_device_id = ad1.id WHERE ada1.action_name = 'MAINTENANCE_START' AND ada1.time < ada.time AND ad1.device_name = ad.device_name AND ada1.TIME > '2011-02-21 11:40:31' AND ada1.TIME < '2011-02-22 23:59') AS Duration,
ada.ID AS ID
FROM acc_device_action AS ada
JOIN acc_device AS ad ON ada.acc_device_id = ad.id
JOIN acc_user AS au ON ada.acc_user_id = au.id
WHERE ada.TIME > '2011-02-21 11:40:31'
AND ada.TIME < '2011-02-22 23:59'
AND ada.action_name = 'MAINTENANCE_STOP'
ORDER BY ad.device_name, ada.TIME ASC ) AS TABLE1
JOIN
(SELECT ada.action_info AS MaintenanceInfo,
ad.device_name AS DeviceName,
ada.action_name,
ada.time AS time,
COALESCE((SELECT min(ada1.ID) FROM acc_device_action AS ada1 JOIN acc_device AS ad1 ON ada1.acc_device_id = ad1.id WHERE ad1.device_name = ad.device_name AND ada1.action_name = 'MAINTENANCE_STOP' AND ada1.time >= ada.time), 0) AS ID
FROM acc_device_action AS ada
JOIN acc_device AS ad ON ada.acc_device_id = ad.id
WHERE ada.TIME > '2011-02-21 11:40:31'
AND ada.TIME < '2011-02-22 23:59'
AND (ada.action_name='MAINTENANCE_START' OR ada.action_name='DEVICE_DEFECT' OR ada.action_name='DEVICE_REPAIRED' OR ada.action_name='MAINTENANCE_STOP' ) ) AS TABLE2
ON TABLE1.ID = TABLE2.ID AND TABLE1.DeviceName = TABLE2.DeviceName
(The hard-coded dates should be replaced by report parameters)
This is likely very inefficient, as it performs a large number of queries on the database. I would have preferred doing just a basic query, and then doing all the logic within birt. However, if this is the only way that it can be done can you at least give me any advice on optimizing the query (if what I did there is at least a bit understandable).
[Updated on: Tue, 01 March 2011 22:03] Report message to a moderator
|
|
|
|
Powered by
FUDForum. Page generated in 0.03260 seconds