[CLOSED] FOC14013: Correlated Subquery Not Allowed in SQL Passthrough (Oracle)

October 15, 2010, 01:11 PM

Folks,
Here's a weird one. Our peoplesoft environment is being upgraded and when testing the new synonyms, I get the following:

 (FOC14013) CORRELATED SUBQUERY ON COLUMN 'SETID' IS NOT SUPPORTED
 (FOC14013) CORRELATED SUBQUERY ON COLUMN 'JOBCODE' IS NOT SUPPORTED
 (FOC14013) CORRELATED SUBQUERY ON COLUMN 'SETID' IS NOT SUPPORTED
 (FOC14013) CORRELATED SUBQUERY ON COLUMN 'DEPTID' IS NOT SUPPORTED
 (FOC14013) CORRELATED SUBQUERY ON COLUMN 'POSITION_NBR' IS NOT SUPPORTED
 (FOC14013) CORRELATED SUBQUERY ON COLUMN 'SETID' IS NOT SUPPORTED
 (FOC14013) CORRELATED SUBQUERY ON COLUMN 'JOBCODE' IS NOT SUPPORTED
 (FOC14013) CORRELATED SUBQUERY ON COLUMN 'SETID' IS NOT SUPPORTED
 (FOC14013) CORRELATED SUBQUERY ON COLUMN 'DEPTID' IS NOT SUPPORTED
 (FOC14013) CORRELATED SUBQUERY ON COLUMN 'POSITION_NBR' IS NOT SUPPORTED

Here is my query:

-SET &SYSTDATE = &DATEYYMD |' '|'23:59:59';

SQL
SELECT A.HRS_JOB_OPENING_ID, D.JOBCODE, A.OPEN_DT, B.DESCR, A.DEPTID, C.DESCR, A.POSITION_NBR, A.RECRUITER_ID, D.REG_TEMP, D.FULL_PART_TIME, D.STD_HOURS, B.COMP_FREQUENCY, B.EEO1CODE
  FROM PS_UHS_JOB_REQ_UPGD A, PS_JOBCODE_TBL B, PS_SET_CNTRL_REC B2, PS_DEPT_TBL C, PS_SET_CNTRL_REC C2, PS_POSITION_DATA D, PS_POSTN_SRCH_QRY D1
  WHERE B.JOBCODE = A.JOBCODE
    AND B2.SETCNTRLVALUE = A.BUSINESS_UNIT
    AND B2.RECNAME = 'JOBCODE_TBL'
    AND B2.SETID = B.SETID
    AND C.DEPTID = A.DEPTID
    AND C2.SETCNTRLVALUE = A.BUSINESS_UNIT
    AND C2.RECNAME = 'DEPT_TBL'
    AND C2.SETID = C.SETID
    AND D.POSITION_NBR = D1.POSITION_NBR
    AND D1.ROWSECCLASS = 'DPALL'
    AND ( B.EFFDT =
        (SELECT MAX(B_ED.EFFDT) FROM PS_JOBCODE_TBL B_ED
        WHERE B.SETID = B_ED.SETID
          AND B.JOBCODE = B_ED.JOBCODE
          AND B_ED.EFFDT <= '&SYSTDATE')
     AND A.JOBCODE = B.JOBCODE
     AND A.DEPTID = C.DEPTID
     AND C.EFFDT =
        (SELECT MAX(C_ED.EFFDT) FROM PS_DEPT_TBL C_ED
        WHERE C.SETID = C_ED.SETID
          AND C.DEPTID = C_ED.DEPTID
          AND C_ED.EFFDT <= '&SYSTDATE')
     AND D.EFFDT =
        (SELECT MAX(D_ED.EFFDT) FROM PS_POSITION_DATA D_ED
        WHERE D.POSITION_NBR = D_ED.POSITION_NBR
          AND D_ED.EFFDT <= '&SYSTDATE')
     AND A.POSITION_NBR = D.POSITION_NBR
     AND A.STATUS_CODE IN ('010','090','020','050','060','070','030','040')
     AND A.DEPTID <> '40902000' )
UNION
SELECT E.HRS_JOB_OPENING_ID, I.JOBCODE, E.STATUS_DT, J.DESCR, I.DEPTID, H.DESCR, I.POSITION_NBR, E.RECRUITER_ID, I.REG_TEMP, I.FULL_PART_TIME, I.STD_HOURS, G.COMP_FREQUENCY, G.EEO1CODE
  FROM PS_UHS_JOB_REQ_UPGD E, PS_PCMH_ER_REQ_POS F, PS_JOBCODE_TBL G, PS_DEPT_TBL H, PS_POSITION_DATA I, PS_POSTN_SRCH_QRY I1, PS_POSN_NOEFFDT_VW J
  WHERE J.POSITION_NBR = F.PCMH_REQ_POS_NBR
    AND I.POSITION_NBR = I1.POSITION_NBR
    AND I1.ROWSECCLASS = 'DPALL'
    AND J.POSITION_NBR = I1.POSITION_NBR
    AND ( E.HRS_JOB_OPENING_ID_A6 = F.JOB_REQ_NBR
     AND G.JOBCODE = E.JOBCODE
     AND G.EFFDT =
        (SELECT MAX(G_ED.EFFDT) FROM PS_JOBCODE_TBL G_ED
        WHERE G.SETID = G_ED.SETID
          AND G.JOBCODE = G_ED.JOBCODE
          AND G_ED.EFFDT <= '&SYSTDATE')
     AND H.DEPTID = I.DEPTID
     AND H.EFFDT =
        (SELECT MAX(H_ED.EFFDT) FROM PS_DEPT_TBL H_ED
        WHERE H.SETID = H_ED.SETID
          AND H.DEPTID = H_ED.DEPTID
          AND H_ED.EFFDT <= '&SYSTDATE')
     AND I.EFFDT =
        (SELECT MAX(I_ED.EFFDT) FROM PS_POSITION_DATA I_ED
        WHERE I.POSITION_NBR = I_ED.POSITION_NBR
          AND I_ED.EFFDT <= '&SYSTDATE')
     AND E.STATUS_CODE IN ('010','090','020','050','060','070','030','040')
     AND F.PCMH_POS_FULL_FLAG = 'O'
     AND F.PCMH_REQ_POS_NBR = I.POSITION_NBR
     AND I.DEPTID <> '40902000' )
  ORDER BY 4
;
TABLE ON TABLE PCHOLD FORMAT ALPHA
END

I've tried setting SQLENGINE to SQLORA, but that introduces a whole new bucket of errors. Any ideas?

- ABT

This message has been edited. Last edited by: ABT,

October 18, 2010, 08:53 AM

ABT

Thanks Waz. I found several issues with the query I inherited (including your point). Here is what I ended with:

-SET &SYSTDATE = &DATEYYMD |' '|'23:59:59';

SET SQLENGINE = SQLORA
SQL SET SERVER PSHRTST
-RUN

SQL SQLORA

SELECT A.HRS_JOB_OPENING_ID, D.JOBCODE, A.OPEN_DT, B.DESCR, A.DEPTID, C.DESCR, A.POSITION_NBR, A.RECRUITER_ID, D.REG_TEMP, D.FULL_PART_TIME, D.STD_HOURS, B.COMP_FREQUENCY, B.EEO1CODE
  FROM PS_UHS_JOB_REQ A, PS_JOBCODE_TBL B, PS_SET_CNTRL_REC B2, PS_DEPT_TBL C, PS_SET_CNTRL_REC C2, PS_POSITION_DATA D
  WHERE B.JOBCODE = A.JOBCODE
    AND B2.SETCNTRLVALUE = A.BUSINESS_UNIT
    AND B2.RECNAME = 'JOBCODE_TBL'
    AND B2.SETID = B.SETID
    AND C.DEPTID = A.DEPTID
    AND C2.SETCNTRLVALUE = A.BUSINESS_UNIT
    AND C2.RECNAME = 'DEPT_TBL'
    AND C2.SETID = C.SETID
    AND ( B.EFFDT =
        (SELECT MAX(B_ED.EFFDT) FROM PS_JOBCODE_TBL B_ED
        WHERE B.SETID = B_ED.SETID
          AND B.JOBCODE = B_ED.JOBCODE
          AND B_ED.EFFDT <= to_timestamp('&SYSTDATE', 'YYYY/MM/DD HH24:MI:SS'))
     AND A.JOBCODE = B.JOBCODE
     AND A.DEPTID = C.DEPTID
     AND C.EFFDT =
        (SELECT MAX(C_ED.EFFDT) FROM PS_DEPT_TBL C_ED
        WHERE C.SETID = C_ED.SETID
          AND C.DEPTID = C_ED.DEPTID
          AND C_ED.EFFDT <= to_timestamp('&SYSTDATE', 'YYYY/MM/DD HH24:MI:SS'))
     AND D.EFFDT =
        (SELECT MAX(D_ED.EFFDT) FROM PS_POSITION_DATA D_ED
        WHERE D.POSITION_NBR = D_ED.POSITION_NBR
          AND D_ED.EFFDT <= to_timestamp('&SYSTDATE', 'YYYY/MM/DD HH24:MI:SS'))
     AND A.POSITION_NBR = D.POSITION_NBR
     AND A.STATUS_CODE IN ('010','090','020','050','060','070','030','040')
     AND A.DEPTID <> '40902000' )
UNION
SELECT E.HRS_JOB_OPENING_ID, I.JOBCODE, E.STATUS_DT, J.DESCR, I.DEPTID, H.DESCR, I.POSITION_NBR, E.RECRUITER_ID, I.REG_TEMP, I.FULL_PART_TIME, I.STD_HOURS, G.COMP_FREQUENCY, G.EEO1CODE
  FROM PS_UHS_JOB_REQ E, PS_JOBCODE_TBL G, PS_DEPT_TBL H, PS_POSITION_DATA I, PS_POSN_NOEFFDT_VW J
  WHERE  (
     G.JOBCODE = E.JOBCODE
     AND G.EFFDT =
        (SELECT MAX(G_ED.EFFDT) FROM PS_JOBCODE_TBL G_ED
        WHERE G.SETID = G_ED.SETID
          AND G.JOBCODE = G_ED.JOBCODE
          AND G_ED.EFFDT <= to_timestamp('&SYSTDATE', 'YYYY/MM/DD HH24:MI:SS'))
     AND H.DEPTID = I.DEPTID
     AND H.EFFDT =
        (SELECT MAX(H_ED.EFFDT) FROM PS_DEPT_TBL H_ED
        WHERE H.SETID = H_ED.SETID
          AND H.DEPTID = H_ED.DEPTID
          AND H_ED.EFFDT <= to_timestamp('&SYSTDATE', 'YYYY/MM/DD HH24:MI:SS'))
     AND I.EFFDT =
        (SELECT MAX(I_ED.EFFDT) FROM PS_POSITION_DATA I_ED
        WHERE I.POSITION_NBR = I_ED.POSITION_NBR
          AND I_ED.EFFDT <= to_timestamp('&SYSTDATE', 'YYYY/MM/DD HH24:MI:SS'))
     AND E.STATUS_CODE IN ('010','090','020','050','060','070','030','040')
     AND I.DEPTID <> '40902000' )
  ORDER BY 4
;
TABLE
ON TABLE HOLD AS ASJOBPOST1
END

Prod:	WebFOCUS 7.6.10/8.1.04	Upgrade:	WebFOCUS 8.2.07	OS:	Linux	Outputs:	HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!