Question from G.Johnson on Jun 22 at 4:35 PM Hello,
Seeking help on rebuilding the following query in PeopleSoft Query manager:
select a.*, b.*
from (select audit_oprid, audit_stamp, audit_actn, emplid, effdt, effseq, action, action_reason, row_number() over (order by rowid) as audit_seq, rowid
from ps_audit_job
where audit_actn = 'K') a,
(select audit_oprid, audit_stamp, audit_actn, emplid, effdt, effseq, action, action_reason, row_number() over (order by rowid) as audit_seq, rowid
from ps_audit_job
where audit_actn = 'N') b
where a.audit_oprid = b.audit_oprid
and a.audit_stamp = b.audit_stamp
and a.emplid = b.emplid
and a.audit_seq = b.audit_seq
and (a.effdt || a.effseq || a.action || a.action_reason) <> (b.effdt || b.effseq || b.action || b.action_reason)
order by a.rowid
The main objective is to create an sequence of events in the audit table to compare changes in effective dates and/or action, action_reason. Without the audit_seq field, joining the ps_audit_table to itself returns cross join results.
| Reply to this email to post your response. __.____._ |   _.____.__ |
No comments:
Post a Comment