Consulting

Results 1 to 4 of 4

Thread: Challenge - Resolve this error: "ORA-00923: FROM keyword not found where expected"

  1. #1

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

    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

  2. #2
    VBAX Newbie
    Joined
    Nov 2016
    Posts
    1
    Location
    Quote Originally Posted by eyesonthebal View Post
    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
    Last edited by Bob Phillips; 05-27-2017 at 10:32 AM. Reason: Added code tags

  3. #3
    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!

  4. #4
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •