SAP (Documents/Screen shots/Complete Solution): Re: [peopletools-l] Subselect in PeopleSoft Query Manager

Reply from mybasicknowledge on Jun 23 at 4:35 PM

You can simply add desired record in Query manager, and it allow you to
create Subquery with WHEER clause where you can define subquery,

For you Column Alias table, you may use Expression to fetch Select and use
as column

---------------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.

__.____._

Manage Settings | Unsubscribe | Create FAQ | Send Feedback