Hi Experts - We are having an issue after upgrading our DB to 12c
When i execute below query in 11G order by clause is working fine with dept id . But same query does not work in 12c which means its not doing the order by and its returning the result with unsorted data.
SELECT
A1.DEPTID,
A1.EMPLID ,
substr(B1.NAME,1,25) ,
A1.FULL_PART_TIME,
A1.STD_HOURS,
A1.BDGT_CATEGORY ,
A1.Empl_Status
FROM PS_JOB_TEST_TEST A1
, PS_DATA_TEST B1
WHERE A1.EMPLID = B1.EMPLID
AND A1.EMPL_RCD = 0
AND (rtrim(A1.lwopIND) <> '' or rtrim(A1.lwopIND) = 'X')
AND A1.ACTION <> 'SEP'
And A1.BDGT_CATEGORY <> 'EFE'
AND A1.DEPTID IN (SELECT DISTINCT ORG.DEPTID FROM PS_TIME_ORGUNT ORG)
AND A1.EFFDT =
(SELECT MAX(A2.EFFDT)
FROM PS_JOB_TEST A2
WHERE A1.EMPLID = A2.EMPLID
AND A1.EMPL_RCD = A2.EMPL_RCD
AND A2.PA_STATUS = 'A'
AND A2.EFFDT <= last_day(to_date('2019/08','YYYY/MM')))
AND A1.EFFSEQ =
(SELECT MAX(A3.EFFSEQ)
FROM PS_JOB_TEST A3
WHERE A1.EMPLID = A3.EMPLID
AND A1.EMPL_RCD = A3.EMPL_RCD
AND A3.PA_STATUS = 'A'
AND A1.EFFDT = A3.EFFDT)
AND NOT EXISTS (SELECT 'X' FROM PS_TIME T1
WHERE T1.EMPLID = A1.EMPLID
AND T1.BRS_PERIOD = '2019/08' T1.ARS_DEPTID=substr(A1.DEPTID,1, 3) )
ORDER BY A1.DEPTID;
What i found is T1.ARS_DEPTID=substr(A1.DEPTID,1, 3) ) is causing an issue. When i remove this particular AND condition it works well. Also i am able to sort with EMPLID.
Since i am using substr to match my dept id and that breaks the sorting in 12c. Can you please help .
Thanks in advance!