Reply from giovanniricupero on Jun 23 at 9:23 AM Hi G.,
In PS query, you can't do inline views. So what I would suggest is create 2
views first and the use those 2 views to work with.
The last criteria where you concatenate a bunch of values, you can add them
in a formula.
So let's assume you have the 2 views (PS_VIEW_A, PS_VIEW_B):
select (all fields from a and b)
from PS_VIEW_A A, PS_VIEW_B b
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 formila1 <> formula2
order by a.rowid
Try it out.
| | | ---------------Original Message---------------
From: G. Johnson
Sent: Friday, June 22, 2012 4:35 PM
Subject: Subselect in PeopleSoft Query Manager
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