Hi all,
Auditors are looking for a one-stop view into the details of our changes, which includes change details coming from both the change task and the approval tables. In short, we need to union rows from each of the two tables to chronologically show the work and the approvals that occurred for each change. I have a sample query below which runs against an ODBC connection, but I need to know if something more efficient can be created 'within' the tool so that a scheduled report can be run and delivered instead of running/exporting SQL query results.
Query:
SELECT ct.dv_change_request AS ChangeNbr,
'Task' as Type,
ct.short_description AS Acty,
ct.dv_state AS Status,
convert(ct.closed_at,timestamp) AS Timestp,
ct.dv_closed_by AS Auth_By,
su.email as EMail
FROM change_task ct,
sys_user su
WHERE su.sys_id = ct.closed_by AND ct.dv_change_request='CHGXXXXXXX'
UNION ALL
SELECT sa.dv_sysapproval AS ChangeNbr,
'Appr' as Type,
sa.u_activity AS Acty,
sa.dv_state AS Status,
convert(sa.sys_updated_on,timestamp) AS Timestp,
sa.dv_approver AS Auth_By,
su.email as EMail
FROM sysapproval_approver sa,
sys_user su
WHERE su.sys_id = sa.approver AND sa.dv_sysapproval='CHGXXXXXXX' AND sa.dv_state='Approved'
ORDER BY 5;
Has anybody had to do something similar? If so, how did you accomplish it?
Thanks in advance,
Chuck H.
