PeopleTools and Lifecycle Management - PSFT (MOSC)

MOSC Banner

PS Query: How do you add an additional criteria within an effective dated subquery

edited Mar 13, 2015 10:04AM in PeopleTools and Lifecycle Management - PSFT (MOSC) 2 commentsAnswered

This returns the EEs last transfer/promotion record.  I want to build this query within Query Manager:


select J.EMPLID, J.DEPTID, J.EFFDT, E.NAME

FROM PS_JOB J, PS_EMPLOYEES E

where J.EMPLID = E.EMPLID

and J.EMPL_RCD = 0

and E.EMPL_RCD = 0

and J.EFFDT = (select MAX(J1.EFFDT) from PS_JOB J1

                where J1.EMPLID = J.EMPLID

                and J1.EMPL_RCD = J.EMPL_RCD

                and J1.ACTION in ('XFR', 'PRO')

                and J1.EFFDT <= GETDATE())


My question is simple but I've been hammering this thing for hours.  How do you add the clause "and J1.ACTION

Tagged:

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center