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.

[drjohnchun]

Can you add it to a Change List View? You'll have to hover over the field value to see all approval history. If it works and shows ALL approvals you need, then we can tackle your ODBC next.

[chuckh_]

Thanks, John.   I did, but it still looks like a single field added (no [+]) plus it doesn't look like there's any other approvals except for mine...   again, in this case, I was last to approve, so it's only showing me.   See below.

[drjohnchun]

Can you confirm if you see any additional approvals in the Approvers Related List on the actual change ticket?

[drjohnchun]

Here's an example of Approval history from a List View:

[chuckh_]

Thanks again, John.   See below.   When I show approval history in the first screenshot, I only show the most recent approval.   Below that, I'm showing ALL approval entries besides the last one---there were 4 before that.

The query to get this data is shown below....

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='CHG0031322' AND sa.dv_state='Approved'

order by 5