PeopleTools and Lifecycle Management - PSFT (MOSC)

MOSC Banner

Subquery return wrong result on PS and Oracle 12.2.0.1 after Upgrade

edited Nov 12, 2019 4:04AM in PeopleTools and Lifecycle Management - PSFT (MOSC) 3 commentsAnswered

We had to change alter session set "_unnest_subquery"=TRUE; from default FASE (recommended by Peoplesoft for Performance reason) in order for our subquery result to be correct. 

This same query work in old release 12.1.0  but not in our new 12.2.0.1.    We are running Peoplesoft HR and having an issue in Absent Calculation nightly jobs.

Please advise.

Here is workaround for now per session.

alter session set "_unnest_subquery"=TRUE;

SQL> alter session set "_unnest_subquery"=TRUE;

Session altered.

SQL> SELECT

COUNT(*)

FROM SYSADM.PS_CSU_AM_Q_JOB_VW JOB

WHERE 1=1

AND JOB.EMPLID = '001354782'

AND EXISTS (SELECT 'X'

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