Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] FOC14013: Correlated Subquery Not Allowed in SQL Passthrough (Oracle)
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] FOC14013: Correlated Subquery Not Allowed in SQL Passthrough (Oracle)
 Login/Join
 
Master
posted
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,


------------------------------------
WF Environment:
------------------------------------
Server/Client, ReportCaster, Dev Studio: 7.6.11
Resource Analyzer, Resource Governor, Library, Maintain, InfoAssist
OS: Windows Server 2003
Application/Web Server: Tomcat 5.5.25
Java: JDK 1.6.0_03
Authentication: LDAP, MRREALM Driver
Output: PDF, EXL2K, HTM

------------------------------------
Databases:
------------------------------------
Oracle 10g
DB2 (AS/400)
MSSQL Server 2005
Access/FoxPro
 
Posts: 561 | Registered: February 03, 2010Reply With QuoteReport This Post
Expert
posted Hide Post
Instead of starting the SQL query with SQL, use SQL SQLORA.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6131 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport This Post
Master
posted Hide Post
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


------------------------------------
WF Environment:
------------------------------------
Server/Client, ReportCaster, Dev Studio: 7.6.11
Resource Analyzer, Resource Governor, Library, Maintain, InfoAssist
OS: Windows Server 2003
Application/Web Server: Tomcat 5.5.25
Java: JDK 1.6.0_03
Authentication: LDAP, MRREALM Driver
Output: PDF, EXL2K, HTM

------------------------------------
Databases:
------------------------------------
Oracle 10g
DB2 (AS/400)
MSSQL Server 2005
Access/FoxPro
 
Posts: 561 | Registered: February 03, 2010Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] FOC14013: Correlated Subquery Not Allowed in SQL Passthrough (Oracle)

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.