PDA

View Full Version : Challenge - Resolve this error: "ORA-00923: FROM keyword not found where expected"


eyesonthebal
05-26-2017, 12:39 PM
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
donethat
05-26-2017, 02:19 PM
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


This is totally by casual observation (obviously I can't run the statement) so here's what I noticed.

I parsed the statement using Notepad++'s Poor Man's T-SQL Formatter.

I believe the FROM clause has 3 opening parenthesis and 4 closing - an imbalance.

Here is the formatted SQL but I couldn't see how to open a "code box" to keep the indenting intact.


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
eyesonthebal
05-30-2017, 05:55 AM
Thank you for the very quick response!

I only have access to my phone at the moment, so I apologize for the brevity, but the FROM clause looks like it could ultimately be written (loosely speaking) in the form:

(((T1 J T2 ON C1) J T3 ON C2) J T4 ON C3) LJ T5 ON C4 =

(((T12) J T3 ON C2) J T4 ON C3) LJ T5 ON C4 =

((T12 J T3 ON C3) J T4 ON C3) LJ T5 ON C4 =

((T123) J T4 ON C3) LJ T5 ON C4 =

(T123 J T4 ON C3) LJ T5 ON C4 =

(T1234) LJ T5 ON C4 =

T1234 LJ T5 ON C4 =

T12345.

This is my current understanding of what the proper syntax for multiple joins should be, although I might be wrong (or maybe this doesn't always hold when working with one-sided joins?).

Thanks again!
eyesonthebal
05-30-2017, 12:54 PM
I discovered what the issue was: Trim(PERSON.FNAME) & " " & Trim(PERSON.LNAME) SubName.

Not sure exactly why this was causing an issue (perhaps either because I didn't use the As keyword or because of my use of the ampersands, or a combination of the two, resulting in some ambiguity), since I'm now racing against time to get this project out, but at least I've found a workaround for this query.

Thank you!