How to UNION data from approval and task tables?

[chuckh_]

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.

[chuckh_]

Just marked as answered and we are good.   Thanks so much, John!   Happy New Year to you, too!

[drjohnchun · Accepted solution]

Ah, to add, you can show Approval History as part of Activity Stream or on its own in a form section, if desired. And marking this answered would be appreciated.