Hi Everyone,

I'm currently coding an Excel workbook that defines and executes a query (SQL 5 below) and uses this query to populate one of the worksheets using recordsets. My problem is that the SQL code I've created for this query involves several nested select statements and the syntax is apparently amiss somewhere, generating error ORA-00923. In particular, the query is comprised of a single nested select statement, which itself is comprised of a few nested select statements (each select statement is shown below). I've encountered this issue before, but I can't determine where I'm going wrong this time around and may have met my SQL Waterloo, so to speak. It would be GREATLY appreciated if someone could take a look at SQL 5 below and identify/explain what's wrong with it; I've tried finding answers online, but so far it's been to no avail. Please note that the queries defined by SQL 1, SQL 2, SQL 3 & SQL 4 all run fine. Is it possible that there is a limit to just how many nested select statements can be included in a query's SQL code? I understand that this is a bit to look over, and don't expect anyone to answer, but I figured I might as well give it a shot anyway, because, at the moment, I'm absolutely stumped! If anyone ends up trying to tackle this problem and has any questions, please don't hesitate to ask. Thank you all, and have a great Memorial weekend!

SQL 5 =

SELECT Q4.CLM_NBR, Q4.CLM_SEQ, Q4.OverpmtAmt, Trim(PAYEE.PAYEE_NAME) ProvPayeeName, Q4.PROCESS_DATE, Q4.GRP_NBR, Q4.DIV_NBR, Q4.SUB_PERS_NBR, Trim(PERSON.FNAME) & " " & Trim(PERSON.LNAME) SubName, Q4.PAT_PERS_NBR, ENROLL.EXTERNAL_ID ExtID, Trim(PERSON1.FNAME) & " " & Trim(PERSON1.LNAME) PatientName, Q4.PayeeDest, CASE WHEN Q4.PayeeDest = 'P' THEN Q4.ProvOpAddr1 ELSE Q4.SubAddr1 END PayeeAddr1, CASE WHEN Q4.PayeeDest = 'P' THEN Q4.ProvOpAddr2 ELSE Q4.SubAddr2 END PayeeAddr2, CASE WHEN Q4.PayeeDest = 'P' THEN Q4.ProvOpCity ELSE Q4.SubCity END PayeeCity, CASE WHEN Q4.PayeeDest = 'P' THEN Q4.ProvOpState ELSE Q4.SubState END PayeeState, CASE WHEN Q4.PayeeDest = 'P' THEN Q4.ProvOpZip ELSE Q4.SubZip END PayeeZip, CASE WHEN Q4.PayeeDest = 'P' THEN Q4.ProvOpAddrEff ELSE Q4.SubAddrEff END PayeeAddrEff, CASE WHEN Q4.PayeeDest = 'P' THEN Q4.ProvOpAddrLim ELSE Q4.SubAddrLim END PayeeAddrLim

FROM (((ELIG.PERSON PERSON INNER JOIN (SQL4) Q4 ON PERSON.PERS_NBR = Q4.SUB_PERS_NBR) INNER JOIN ELIG.ENROLL_IN_FAMILY ENROLL ON (Q4.SUB_PERS_NBR = ENROLL.SUB_PERS_NBR) AND (Q4.DIV_NBR = ENROLL.DIV_NBR) AND (Q4.GRP_NBR = ENROLL.GRP_NBR)) INNER JOIN ELIG.PERSON PERSON1 ON Q4.PAT_PERS_NBR = PERSON1.PERS_NBR) LEFT JOIN PROV.PR_PAYEE PAYEE ON Q4.PAYEE_ID = PAYEE.PAYEE_ID

GROUP BY Q4.CLM_NBR, Q4.CLM_SEQ, Q4.OverpmtAmt, Trim(PAYEE.PAYEE_NAME), Q4.PROCESS_DATE, Q4.GRP_NBR, Q4.DIV_NBR, Q4.SUB_PERS_NBR, Trim(PERSON.FNAME) & " " & Trim(PERSON.LNAME), Q4.PAT_PERS_NBR, ENROLL.EXTERNAL_ID, Trim(PERSON1.FNAME) & " " & Trim(PERSON1.LNAME), Q4.PayeeDest, CASE WHEN Q4.PayeeDest = 'P' THEN Q4.ProvOpAddr1 ELSE Q4.SubAddr1 END, CASE WHEN Q4.PayeeDest = 'P' THEN Q4.ProvOpAddr2 ELSE Q4.SubAddr2 END, CASE WHEN Q4.PayeeDest = 'P' THEN Q4.ProvOpCity ELSE Q4.SubCity END, CASE WHEN Q4.PayeeDest = 'P' THEN Q4.ProvOpState ELSE Q4.SubState END, CASE WHEN Q4.PayeeDest = 'P' THEN Q4.ProvOpZip ELSE Q4.SubZip END, CASE WHEN Q4.PayeeDest = 'P' THEN Q4.ProvOpAddrEff ELSE Q4.SuBAddrEff END, CASE WHEN Q4.PayeeDest = 'P' THEN Q4.ProvOpAddrLim ELSE Q4.SubAddrLim END
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL1 =

SELECT CLAIM.CLM_NBR, CLAIM.CLM_SEQ, -CLAIM.TOTAL_PLAN_PMT OverpmtAmt, CLAIM.PROV_ROOT_ID, CLAIM.PROV_OPS_NBR, CLAIM.PAYEE_ID, CLAIM.GRP_NBR, CLAIM.DIV_NBR, CLAIM.SUB_PERS_NBR, CLAIM.PAT_PERS_NBR, CLAIM.PROCESS_DATE, CASE WHEN CLAIM.EXM_SET_PMT_DEST_FLAG Is Not Null THEN CLAIM.EXM_SET_PMT_DEST_FLAG ELSE CLAIM.PMT_DEST END PayeeDest, MAX(USES_ADDR.EFF_DATE) MaxOfEFF_DATE

FROM ADJUD.MASTER_CLAIM CLAIM INNER JOIN PROV.PR_OPS_USES_ADDRESS USES_ADDR ON (CLAIM.PROV_ROOT_ID = USES_ADDR.PROV_ROOT_ID) AND (CLAIM.PROV_OPS_NBR = USES_ADDR.OPS_NBR)

WHERE ((CLAIM.CLAIM_STATUS = 'A') AND (CLAIM.PREAUTH_STATUS In ('0','2')) AND (CLAIM.TOTAL_PLAN_PMT < 0) AND (CLAIM.PROCESS_DATE < TO_DATE('1/10/2017', 'MM-DD-YYYY')))

GROUP BY CLAIM.CLM_NBR, CLAIM.CLM_SEQ, -CLAIM.TOTAL_PLAN_PMT, CLAIM.PROV_ROOT_ID, CLAIM.PROV_OPS_NBR, CLAIM.PAYEE_ID, CLAIM.GRP_NBR, CLAIM.DIV_NBR, CLAIM.SUB_PERS_NBR, CLAIM.PAT_PERS_NBR, CLAIM.PROCESS_DATE, CASE WHEN CLAIM.EXM_SET_PMT_DEST_FLAG Is Not Null THEN CLAIM.EXM_SET_PMT_DEST_FLAG ELSE CLAIM.PMT_DEST END
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL2 =

SELECT Q1.CLM_NBR, Q1.CLM_SEQ, Q1.OverpmtAmt, Q1.PAYEE_ID, Q1.GRP_NBR, Q1.DIV_NBR, Q1.SUB_PERS_NBR, Q1.PAT_PERS_NBR, Q1.PROCESS_DATE, Q1.PayeeDest, Trim(PROVADDR.ADDR_L1) ProvOpAddr1, Trim(PROVADDR.ADDR_L2) ProvOpAddr2, Trim(PROVADDR.ADDR_CITY) ProvOpCity, Trim(PROVADDR.ADDR_STATE) ProvOpState, Trim(PROVADDR.ADDR_ZIP) ProvOpZip, USES_ADDR.EFF_DATE ProvOpAddrEff, USES_ADDR.LIM_DATE ProvOpAddrLim

FROM ((SQL1) Q1 INNER JOIN PROV.PR_OPS_USES_ADDRESS USES_ADDR ON (Q1.PROV_ROOT_ID = USES_ADDR.PROV_ROOT_ID) AND (Q1.PROV_OPS_NBR = USES_ADDR.OPS_NBR) AND (Q1.MaxOfEFF_DATE = USES_ADDR.EFF_DATE)) INNER JOIN PROV.PR_ADDRESS PROVADDR ON USES_ADDR.ADD_REC_ID = PROVADDR.REC_ID

GROUP BY Q1.CLM_NBR, Q1.CLM_SEQ, Q1.OverpmtAmt, Q1.PAYEE_ID, Q1.GRP_NBR, Q1.DIV_NBR, Q1.SUB_PERS_NBR, Q1.PAT_PERS_NBR, Q1.PROCESS_DATE, Q1.PayeeDest, Trim(PROVADDR.ADDR_L1), Trim(PROVADDR.ADDR_L2), Trim(PROVADDR.ADDR_CITY), Trim(PROVADDR.ADDR_STATE), Trim(PROVADDR.ADDR_ZIP), USES_ADDR.EFF_DATE, USES_ADDR.LIM_DATE
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL3 =

SELECT Q2.CLM_NBR, Q2.CLM_SEQ, Q2.OverpmtAmt, Q2.PAYEE_ID, Q2.GRP_NBR, Q2.DIV_NBR, Q2.SUB_PERS_NBR, Q2.PAT_PERS_NBR, Q2.PROCESS_DATE, Q2.PayeeDest, Q2.ProvOpAddr1, Q2.ProvOpAddr2, Q2.ProvOpCity, Q2.ProvOpState, Q2.ProvOpZip, Q2.ProvOpAddrEff, Q2.ProvOpAddrLim, Max(ELIGADDR.EFF_DATE) MaxEffAddrDate

FROM (SQL2) Q2 INNER JOIN ELIG.ADDRESS ELIGADDR ON (Q2.GRP_NBR = ELIGADDR.GRP_NBR) AND (Q2.DIV_NBR = ELIGADDR.DIV_NBR) AND (Q2.SUB_PERS_NBR = ELIGADDR.SUB_PERS_NBR)

GROUP BY Q2.CLM_NBR, Q2.CLM_SEQ, Q2.OverpmtAmt, Q2.PAYEE_ID, Q2.GRP_NBR, Q2.DIV_NBR, Q2.SUB_PERS_NBR, Q2.PAT_PERS_NBR, Q2.PROCESS_DATE, Q2.PayeeDest, Q2.ProvOpAddr1, Q2.ProvOpAddr2, Q2.ProvOpCity, Q2.ProvOpState, Q2.ProvOpZip, Q2.ProvOpAddrEff, Q2.ProvOpAddrLim
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL4 =

SELECT Q3.CLM_NBR, Q3.CLM_SEQ, Q3.OverpmtAmt, Q3.PAYEE_ID, Q3.GRP_NBR, Q3.DIV_NBR, Q3.SUB_PERS_NBR, Q3.PAT_PERS_NBR, Q3.PROCESS_DATE, Q3.PayeeDest, Q3.ProvOpAddr1, Q3.ProvOpAddr2, Q3.ProvOpCity, Q3.ProvOpState, Q3.ProvOpZip, Q3.ProvOpAddrEff, Q3.ProvOpAddrLim, Trim(ELIGADDR.ADDR_LINE1) SubAddr1, Trim(ELIGADDR.ADDR_LINE2) SubAddr2, Trim(ELIGADDR.ADDR_CITY) SubCity, Trim(ELIGADDR.ADDR_STATE) SubState, Trim(ELIGADDR.ADDR_ZIP) SubZip, ELIGADDR.EFF_DATE SubAddrEff, ELIGADDR.LIM_DATE SubAddrLim

FROM (SQL3) Q3 INNER JOIN ELIG.ADDRESS ELIGADDR ON (Q3.GRP_NBR = ELIGADDR.GRP_NBR) AND (Q3.DIV_NBR = ELIGADDR.DIV_NBR) AND (Q3.SUB_PERS_NBR = ELIGADDR.SUB_PERS_NBR) AND (Q3.MaxEffAddrDate = ELIGADDR.EFF_DATE)

GROUP BY Q3.CLM_NBR, Q3.CLM_SEQ, Q3.OverpmtAmt, Q3.PAYEE_ID, Q3.GRP_NBR, Q3.DIV_NBR, Q3.SUB_PERS_NBR, Q3.PAT_PERS_NBR, Q3.PROCESS_DATE, Q3.PayeeDest, Q3.ProvOpAddr1, Q3.ProvOpAddr2, Q3.ProvOpCity, Q3.ProvOpState, Q3.ProvOpZip, Q3.ProvOpAddrEff, Q3.ProvOpAddrLim, Trim(ELIGADDR.ADDR_LINE1), Trim(ELIGADDR.ADDR_LINE2), Trim(ELIGADDR.ADDR_CITY), Trim(ELIGADDR.ADDR_STATE), Trim(ELIGADDR.ADDR_ZIP), ELIGADDR.EFF_DATE, ELIGADDR.LIM_DATE