Subquery return wrong result on PS and Oracle 12.2.0.1 after Upgrade
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'