CODEWITHSUNDEEP
sqloracle-databasesyntaxora-00907
lorynhope
lorynhope

Reputation: 13

SQL query in Excel throwing 'Missing Right Parenthesis' error

Disclaimer: I am not a developer, I have no intimate knowledge of SQL.

I was handed an Excel document that has ODBC connections to our Oracle database. There are several queries, and this is not technology that I've used for this purpose before. I've figured out all queries except for one, and I keep getting an error:

[Oracle][ODBC][Ora]ORA-00907: missing right parenthesis error.

I believe I've validated all parentheses, and I've validated the presence of single quotes where appropriate - I cannot figure out where a syntax error would be. Is there anyone who can help?

With tblCCR as 
(
    Select 
        MCS_SVC_REQ.MSR_SR_NUM, MCS_SVC_REQ_ACTION.msra_model_num 
    from 
        VRSC.MCS_SVC_REQ 
    inner join 
        VRSC.MCS_SVC_REQ_ACTION on MCS_SVC_REQ.MSR_SR_NUM = MCS_SVC_REQ_ACTION.MSRA_SR_NUM 
    where 
        MCS_SVC_REQ_ACTION.msra_model_num in ('CANADACARDREADER','HCR CARDREADER')
),
SR_Parts1 as 
(
    Select 
        MCS_SVC_REQ_ACTION.MSRA_SR_NUM as MSR_SR_NUM, MCS_PARTS_ACTIVITY.MPA_PART_NUM 
    from 
        VRSC.MCS_SVC_REQ_ACTION 
    inner join  
        VRSC.MCS_PARTS_ACTIVITY ON MCS_SVC_REQ_ACTION.MSRA_ACTION_ID = MCS_PARTS_ACTIVITY.MPA_ACTION_ID
),
SR_Parts as 
(
     Select 
         SR_Parts1.MSR_SR_NUM, 'Parts' as Parts 
     from 
         SR_Parts1 
     group by 
         SR_Parts1.MSR_SR_NUM
)
Select MCS_SVC_REQ.MSR_SR_NUM,
    MCS_SVC_REQ.MSR_SITE_ID as SITE,
    Case 
        When MCS_SVC_REQ.MSR_SR_STATUS = 0 then 'Open'
        When MCS_SVC_REQ.MSR_SR_STATUS = 1 then 'Dispatched'
        When MCS_SVC_REQ.MSR_SR_STATUS = 2 then 'Pending'
        When MCS_SVC_REQ.MSR_SR_STATUS = 3 then 'Force Closed'
        When MCS_SVC_REQ.MSR_SR_STATUS = 4 then 'Canceled'
        When MCS_SVC_REQ.MSR_SR_STATUS = 5 then 'Closed Pending'
        When MCS_SVC_REQ.MSR_SR_STATUS = 6 then 'Closed'
        When MCS_SVC_REQ.MSR_SR_STATUS = 7 then 'Service Confirmation'
        When MCS_SVC_REQ.MSR_SR_STATUS = 8 then 'Closed Web'
        End as STATUS,
    MCS_SVC_REQ.MSR_SP_ID as ASC_ACCT,
    CONTRACTORS.CONTR_NAME as ASC_NAME,
    MCS_SVC_REQ.MSR_LAST_EDIT as LAST_EDITED,
    MCS_SVC_REQ.MSR_USR_ID,
    CUSTOMERS.CUST_ID as CUST_ACCT,
    CUSTOMERS.CUST_NAME,
    MCS_SVC_REQ.MSR_NET30_CLOSE_DATE,
    MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,
    TO_CHAR(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE, 'HH24:MI:SS') as Time_of_Day,
    Case 
        when MCS_SVC_REQ.MSR_CLOSED_WEB_DATE is Null then EXTRACT (DAY FROM (SYSDATE - MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) DAY TO SECOND)
        else EXTRACT (DAY FROM (SYSDATE - MCS_SVC_REQ.MSR_CLOSED_WEB_DATE) DAY TO SECOND)
        end as Time_Elapsed,
    MA.MSRA_call_type as CALL_TYPE,
    MA.MSRA_Bill_Code as BILL_CODE,
    COUNT(MA.MSRA_ACTION_ID) as LINES,
    MCS_SVC_REQ.MSR_ESC_REVIEW,
    Case 
        when EXTRACT (DAY FROM (SYSDATE - MCS_SVC_REQ.MSR_LAST_EDIT) DAY TO SECOND) between 0 and 1 then '01 - 24 hrs.'
        when EXTRACT (DAY FROM (SYSDATE - MCS_SVC_REQ.MSR_LAST_EDIT) DAY TO SECOND) = 2 then '02 - 48 hrs.'
        when EXTRACT (DAY FROM (SYSDATE - MCS_SVC_REQ.MSR_LAST_EDIT) DAY TO SECOND) = 3 then '03 - 72 hrs.'
        when EXTRACT (DAY FROM (SYSDATE - MCS_SVC_REQ.MSR_LAST_EDIT) DAY TO SECOND) = 4 then '04 - 96 hrs.'
        when EXTRACT (DAY FROM (SYSDATE - MCS_SVC_REQ.MSR_LAST_EDIT) DAY TO SECOND) between 5 and 10 then '05 - 5 to 10 days'
        when EXTRACT (DAY FROM (SYSDATE - MCS_SVC_REQ.MSR_LAST_EDIT) DAY TO SECOND) between 11 and 20 then '06 - 11 to 20 days'
        when EXTRACT (DAY FROM (SYSDATE - MCS_SVC_REQ.MSR_LAST_EDIT) DAY TO SECOND) between 21 and 30 then '07 - 21 to 30 days'
        when EXTRACT (DAY FROM (SYSDATE - MCS_SVC_REQ.MSR_LAST_EDIT) DAY TO SECOND) between 31 and 44 then '08 - 31 to 44 days'
        when EXTRACT (DAY FROM (SYSDATE - MCS_SVC_REQ.MSR_LAST_EDIT) DAY TO SECOND) between 45 and 60 then '09 - 45 to 60 days'
        when EXTRACT (DAY FROM (SYSDATE - MCS_SVC_REQ.MSR_LAST_EDIT) DAY TO SECOND) > 60 then '10 - 60+ days'
        else 'Update'
        end as AGE_CATEGORY,
    MCS_SVC_REQ.MSR_EST_AMT as ESTIMATED_VALUE,
    tblCCR.msra_model_num as CCR,
    NVL(SR_Parts.Parts,'No Parts') as PARTS,
    Case when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('01/01/2021','MM/DD/YYYY') and TO_DATE('01/09/2021','MM/DD/YYYY') then 'Wk 01'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('01/10/2021’,'MM/DD/YYYY') and TO_DATE('01/16/2021’,'MM/DD/YYYY') then 'Wk 02'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('01/17/2021','MM/DD/YYYY') and TO_DATE('01/23/2021’,'MM/DD/YYYY') then 'Wk 03'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('01/24/2021','MM/DD/YYYY') and TO_DATE('01/30/2021','MM/DD/YYYY') then 'Wk 04'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE(‘01/31/2021','MM/DD/YYYY') and TO_DATE('02/06/2021','MM/DD/YYYY') then 'Wk 05'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('02/07/2021','MM/DD/YYYY') and TO_DATE('02/13/2021','MM/DD/YYYY') then 'Wk 06'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('02/14/2021','MM/DD/YYYY') and TO_DATE('02/20/2021','MM/DD/YYYY') then 'Wk 07'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('02/21/2021','MM/DD/YYYY') and TO_DATE('02/27/2021','MM/DD/YYYY') then 'Wk 08'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('02/28/2021','MM/DD/YYYY') and TO_DATE('03/06/2021','MM/DD/YYYY') then 'Wk 09'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('03/07/2021','MM/DD/YYYY') and TO_DATE('03/13/2021','MM/DD/YYYY') then 'Wk 10'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('03/14/2021','MM/DD/YYYY') and TO_DATE('03/20/2021','MM/DD/YYYY') then 'Wk 11'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('03/21/2021','MM/DD/YYYY') and TO_DATE('03/27/2021','MM/DD/YYYY') then 'Wk 12'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('03/28/2021','MM/DD/YYYY') and TO_DATE(‘04/03/2021','MM/DD/YYYY') then 'Wk 13'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE(‘04/04/2021','MM/DD/YYYY') and TO_DATE('04/10/2021','MM/DD/YYYY') then 'Wk 14'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('04/11/2021','MM/DD/YYYY') and TO_DATE('04/17/2021','MM/DD/YYYY') then 'Wk 15'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('04/18/2021','MM/DD/YYYY') and TO_DATE('04/24/2021','MM/DD/YYYY') then 'Wk 16'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('04/25/2021','MM/DD/YYYY') and TO_DATE(‘05/01/2021','MM/DD/YYYY') then 'Wk 17'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE(‘05/02/2021','MM/DD/YYYY') and TO_DATE('05/08/2021','MM/DD/YYYY') then 'Wk 18'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('05/09/2021','MM/DD/YYYY') and TO_DATE('05/15/2021','MM/DD/YYYY') then 'Wk 19'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('05/16/2021','MM/DD/YYYY') and TO_DATE('05/22/2021','MM/DD/YYYY') then 'Wk 20'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('05/23/2021','MM/DD/YYYY') and TO_DATE('05/29/2021','MM/DD/YYYY') then 'Wk 21'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('05/30/2021','MM/DD/YYYY') and TO_DATE(‘06/05/2021','MM/DD/YYYY') then 'Wk 22'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE(‘06/06/2021','MM/DD/YYYY') and TO_DATE('06/12/2021','MM/DD/YYYY') then 'Wk 23'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('06/13/2021','MM/DD/YYYY') and TO_DATE('06/19/2021','MM/DD/YYYY') then 'Wk 24'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('06/20/2021','MM/DD/YYYY') and TO_DATE('06/26/2021','MM/DD/YYYY') then 'Wk 25'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('06/27/2021','MM/DD/YYYY') and TO_DATE('07/03/2021','MM/DD/YYYY') then 'Wk 26'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('07/04/2021','MM/DD/YYYY') and TO_DATE('07/10/2021','MM/DD/YYYY') then 'Wk 27'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('07/11/2021','MM/DD/YYYY') and TO_DATE('07/17/2021','MM/DD/YYYY') then 'Wk 28'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('07/18/2021','MM/DD/YYYY') and TO_DATE('07/24/2021','MM/DD/YYYY') then 'Wk 29'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('07/25/2021','MM/DD/YYYY') and TO_DATE('07/31/2021','MM/DD/YYYY') then 'Wk 30'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('08/01/2021','MM/DD/YYYY') and TO_DATE('08/07/2021','MM/DD/YYYY') then 'Wk 31'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('08/08/2021','MM/DD/YYYY') and TO_DATE('08/14/2021','MM/DD/YYYY') then 'Wk 32'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('08/15/2021','MM/DD/YYYY') and TO_DATE('08/21/2021','MM/DD/YYYY') then 'Wk 33'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('08/22/2021','MM/DD/YYYY') and TO_DATE('08/28/2021','MM/DD/YYYY') then 'Wk 34'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('08/29/2021','MM/DD/YYYY') and TO_DATE('09/04/2021','MM/DD/YYYY') then 'Wk 35'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('09/05/2021','MM/DD/YYYY') and TO_DATE('09/11/2021','MM/DD/YYYY') then 'Wk 36'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('09/12/2021','MM/DD/YYYY') and TO_DATE('09/18/2021','MM/DD/YYYY') then 'Wk 37'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('09/19/2021','MM/DD/YYYY') and TO_DATE('09/25/2021','MM/DD/YYYY') then 'Wk 38'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('09/26/2021','MM/DD/YYYY') and TO_DATE('10/02/2021','MM/DD/YYYY') then 'Wk 39'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('10/03/2021','MM/DD/YYYY') and TO_DATE('10/09/2021','MM/DD/YYYY') then 'Wk 40'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('10/10/2021','MM/DD/YYYY') and TO_DATE('10/16/2021','MM/DD/YYYY') then 'Wk 41'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('10/17/2021','MM/DD/YYYY') and TO_DATE('10/23/2021','MM/DD/YYYY') then 'Wk 42'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('10/24/2021','MM/DD/YYYY') and TO_DATE('10/30/2021','MM/DD/YYYY') then 'Wk 43'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('10/31/2021','MM/DD/YYYY') and TO_DATE('11/06/2021','MM/DD/YYYY') then 'Wk 44'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('11/07/2021','MM/DD/YYYY') and TO_DATE('11/13/2021','MM/DD/YYYY') then 'Wk 45'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('11/14/2021','MM/DD/YYYY') and TO_DATE('11/20/2021','MM/DD/YYYY') then 'Wk 46'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('11/21/2021','MM/DD/YYYY') and TO_DATE('11/27/2021','MM/DD/YYYY') then 'Wk 47'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('11/28/2021','MM/DD/YYYY') and TO_DATE('12/04/2021','MM/DD/YYYY') then 'Wk 48'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('12/05/2021','MM/DD/YYYY') and TO_DATE('12/11/2021','MM/DD/YYYY') then 'Wk 49'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('12/12/2021','MM/DD/YYYY') and TO_DATE('12/18/2021','MM/DD/YYYY') then 'Wk 50'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('12/19/2021','MM/DD/YYYY') and TO_DATE('12/25/2021','MM/DD/YYYY') then 'Wk 51'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('12/26/2021','MM/DD/YYYY') and TO_DATE('12/31/2021','MM/DD/YYYY') then 'Wk 52'
            else 'Updt_Qry'
            end as Fiscal_Week
from VRSC.MCS_SVC_REQ
    left join VRSC.CUSTOMERS on MCS_SVC_REQ.MSR_CUST_ID = CUSTOMERS.CUST_ID
    left join VRSC.CONTRACTORS on MCS_SVC_REQ.MSR_SP_ID = CONTRACTORS.CONTR_ID
    left join tblCCR on MCS_SVC_REQ.MSR_SR_NUM = tblCCR.MSR_SR_NUM
    left join SR_Parts on MCS_SVC_REQ.MSR_SR_NUM = SR_Parts.MSR_SR_NUM
    left join VRSC.MCS_SVC_REQ_ACTION MA on MCS_SVC_REQ.MSR_SR_NUM = MA.MSRA_SR_NUM
   
Where 1=1
and MCS_SVC_REQ.MSR_SR_STATUS not in (0,1,2)
and MA.MSRA_Bill_Code in ('W')
and NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE, MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) > SYSDATE -21
and MA.MSRA_call_type not in ('RG','CO')

group by MCS_SVC_REQ.MSR_SR_NUM, MCS_SVC_REQ.MSR_SITE_ID, Case When MCS_SVC_REQ.MSR_SR_STATUS = 0 then 'Open' When MCS_SVC_REQ.MSR_SR_STATUS = 1 then 'Dispatched' When MCS_SVC_REQ.MSR_SR_STATUS = 2 then 'Pending' When MCS_SVC_REQ.MSR_SR_STATUS = 3 then 'Force Closed' When MCS_SVC_REQ.MSR_SR_STATUS = 4 then 'Canceled' When MCS_SVC_REQ.MSR_SR_STATUS = 5 then 'Closed Pending' When MCS_SVC_REQ.MSR_SR_STATUS = 6 then 'Closed' When MCS_SVC_REQ.MSR_SR_STATUS = 7 then 'Service Confirmation' When MCS_SVC_REQ.MSR_SR_STATUS = 8 then 'Closed Web' End, MCS_SVC_REQ.MSR_SP_ID, CONTRACTORS.CONTR_NAME, 
MCS_SVC_REQ.MSR_LAST_EDIT, MCS_SVC_REQ.MSR_USR_ID, CUSTOMERS.CUST_ID, CUSTOMERS.CUST_NAME, MCS_SVC_REQ.MSR_NET30_CLOSE_DATE, 
MCS_SVC_REQ.MSR_CLOSED_WEB_DATE, TO_CHAR(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE, 'HH24:MM:SS'), Case when MCS_SVC_REQ.MSR_CLOSED_WEB_DATE is Null then EXTRACT (DAY FROM (SYSDATE - MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) DAY TO SECOND) else EXTRACT (DAY FROM (SYSDATE - MCS_SVC_REQ.MSR_CLOSED_WEB_DATE) DAY TO SECOND) end, MA.MSRA_call_type, MA.MSRA_Bill_Code, 
MCS_SVC_REQ.MSR_ESC_REVIEW, Case when EXTRACT (DAY FROM (SYSDATE - MCS_SVC_REQ.MSR_LAST_EDIT) DAY TO SECOND) between 0 and 1 then '01 - 24 hrs.' when EXTRACT (DAY FROM (SYSDATE - MCS_SVC_REQ.MSR_LAST_EDIT) DAY TO SECOND) = 2 then '02 - 48 hrs.' when EXTRACT (DAY FROM (SYSDATE - MCS_SVC_REQ.MSR_LAST_EDIT) DAY TO SECOND) = 3 then '03 - 72 hrs.' when EXTRACT (DAY FROM (SYSDATE - MCS_SVC_REQ.MSR_LAST_EDIT) DAY TO SECOND) = 4 then '04 - 96 hrs.' when EXTRACT (DAY FROM (SYSDATE - MCS_SVC_REQ.MSR_LAST_EDIT) DAY TO SECOND) between 5 and 10 then '05 - 5 to 10 days' when EXTRACT (DAY FROM (SYSDATE - MCS_SVC_REQ.MSR_LAST_EDIT) DAY TO SECOND) between 11 and 20 then '06 - 11 to 20 days' when EXTRACT (DAY FROM (SYSDATE - MCS_SVC_REQ.MSR_LAST_EDIT) DAY TO SECOND) between 21 and 30 then '07 - 21 to 30 days' when EXTRACT (DAY FROM (SYSDATE - MCS_SVC_REQ.MSR_LAST_EDIT) DAY TO SECOND) between 31 and 44 then '08 - 31 to 44 days' when EXTRACT (DAY FROM (SYSDATE - MCS_SVC_REQ.MSR_LAST_EDIT) DAY TO SECOND) between 45 and 60 then '09 - 45 to 60 days' when EXTRACT (DAY FROM (SYSDATE - MCS_SVC_REQ.MSR_LAST_EDIT) DAY TO SECOND) > 60 then '10 - 60+ days' else 'Update' end, MCS_SVC_REQ.MSR_EST_AMT, tblCCR.msra_model_num, NVL(SR_Parts.Parts,'No Parts'), Case
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('01/01/2021','MM/DD/YYYY') and TO_DATE('01/09/2021','MM/DD/YYYY') then 'Wk 01'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('01/10/2021’,'MM/DD/YYYY') and TO_DATE('01/16/2021’,'MM/DD/YYYY') then 'Wk 02'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('01/17/2021','MM/DD/YYYY') and TO_DATE('01/23/2021’,'MM/DD/YYYY') then 'Wk 03'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('01/24/2021','MM/DD/YYYY') and TO_DATE('01/30/2021','MM/DD/YYYY') then 'Wk 04'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE(‘01/31/2021','MM/DD/YYYY') and TO_DATE('02/06/2021','MM/DD/YYYY') then 'Wk 05'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('02/07/2021','MM/DD/YYYY') and TO_DATE('02/13/2021','MM/DD/YYYY') then 'Wk 06'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('02/14/2021','MM/DD/YYYY') and TO_DATE('02/20/2021','MM/DD/YYYY') then 'Wk 07'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('02/21/2021','MM/DD/YYYY') and TO_DATE('02/27/2021','MM/DD/YYYY') then 'Wk 08'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('02/28/2021','MM/DD/YYYY') and TO_DATE('03/06/2021','MM/DD/YYYY') then 'Wk 09'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('03/07/2021','MM/DD/YYYY') and TO_DATE('03/13/2021','MM/DD/YYYY') then 'Wk 10'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('03/14/2021','MM/DD/YYYY') and TO_DATE('03/20/2021','MM/DD/YYYY') then 'Wk 11'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('03/21/2021','MM/DD/YYYY') and TO_DATE('03/27/2021','MM/DD/YYYY') then 'Wk 12'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('03/28/2021','MM/DD/YYYY') and TO_DATE(‘04/03/2021','MM/DD/YYYY') then 'Wk 13'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE(‘04/04/2021','MM/DD/YYYY') and TO_DATE('04/10/2021','MM/DD/YYYY') then 'Wk 14'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('04/11/2021','MM/DD/YYYY') and TO_DATE('04/17/2021','MM/DD/YYYY') then 'Wk 15'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('04/18/2021','MM/DD/YYYY') and TO_DATE('04/24/2021','MM/DD/YYYY') then 'Wk 16'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('04/25/2021','MM/DD/YYYY') and TO_DATE(‘05/01/2021','MM/DD/YYYY') then 'Wk 17'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE(‘05/02/2021','MM/DD/YYYY') and TO_DATE('05/08/2021','MM/DD/YYYY') then 'Wk 18'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('05/09/2021','MM/DD/YYYY') and TO_DATE('05/15/2021','MM/DD/YYYY') then 'Wk 19'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('05/16/2021','MM/DD/YYYY') and TO_DATE('05/22/2021','MM/DD/YYYY') then 'Wk 20'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('05/23/2021','MM/DD/YYYY') and TO_DATE('05/29/2021','MM/DD/YYYY') then 'Wk 21'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('05/30/2021','MM/DD/YYYY') and TO_DATE(‘06/05/2021','MM/DD/YYYY') then 'Wk 22'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE(‘06/06/2021','MM/DD/YYYY') and TO_DATE('06/12/2021','MM/DD/YYYY') then 'Wk 23'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('06/13/2021','MM/DD/YYYY') and TO_DATE('06/19/2021','MM/DD/YYYY') then 'Wk 24'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('06/20/2021','MM/DD/YYYY') and TO_DATE('06/26/2021','MM/DD/YYYY') then 'Wk 25'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('06/27/2021','MM/DD/YYYY') and TO_DATE('07/03/2021','MM/DD/YYYY') then 'Wk 26'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('07/04/2021','MM/DD/YYYY') and TO_DATE('07/10/2021','MM/DD/YYYY') then 'Wk 27'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('07/11/2021','MM/DD/YYYY') and TO_DATE('07/17/2021','MM/DD/YYYY') then 'Wk 28'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('07/18/2021','MM/DD/YYYY') and TO_DATE('07/24/2021','MM/DD/YYYY') then 'Wk 29'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('07/25/2021','MM/DD/YYYY') and TO_DATE('07/31/2021','MM/DD/YYYY') then 'Wk 30'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('08/01/2021','MM/DD/YYYY') and TO_DATE('08/07/2021','MM/DD/YYYY') then 'Wk 31'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('08/08/2021','MM/DD/YYYY') and TO_DATE('08/14/2021','MM/DD/YYYY') then 'Wk 32'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('08/15/2021','MM/DD/YYYY') and TO_DATE('08/21/2021','MM/DD/YYYY') then 'Wk 33'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('08/22/2021','MM/DD/YYYY') and TO_DATE('08/28/2021','MM/DD/YYYY') then 'Wk 34'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('08/29/2021','MM/DD/YYYY') and TO_DATE('09/04/2021','MM/DD/YYYY') then 'Wk 35'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('09/05/2021','MM/DD/YYYY') and TO_DATE('09/11/2021','MM/DD/YYYY') then 'Wk 36'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('09/12/2021','MM/DD/YYYY') and TO_DATE('09/18/2021','MM/DD/YYYY') then 'Wk 37'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('09/19/2021','MM/DD/YYYY') and TO_DATE('09/25/2021','MM/DD/YYYY') then 'Wk 38'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('09/26/2021','MM/DD/YYYY') and TO_DATE('10/02/2021','MM/DD/YYYY') then 'Wk 39'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('10/03/2021','MM/DD/YYYY') and TO_DATE('10/09/2021','MM/DD/YYYY') then 'Wk 40'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('10/10/2021','MM/DD/YYYY') and TO_DATE('10/16/2021','MM/DD/YYYY') then 'Wk 41'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('10/17/2021','MM/DD/YYYY') and TO_DATE('10/23/2021','MM/DD/YYYY') then 'Wk 42'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('10/24/2021','MM/DD/YYYY') and TO_DATE('10/30/2021','MM/DD/YYYY') then 'Wk 43'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('10/31/2021','MM/DD/YYYY') and TO_DATE('11/06/2021','MM/DD/YYYY') then 'Wk 44'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('11/07/2021','MM/DD/YYYY') and TO_DATE('11/13/2021','MM/DD/YYYY') then 'Wk 45'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('11/14/2021','MM/DD/YYYY') and TO_DATE('11/20/2021','MM/DD/YYYY') then 'Wk 46'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('11/21/2021','MM/DD/YYYY') and TO_DATE('11/27/2021','MM/DD/YYYY') then 'Wk 47'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('11/28/2021','MM/DD/YYYY') and TO_DATE('12/04/2021','MM/DD/YYYY') then 'Wk 48'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('12/05/2021','MM/DD/YYYY') and TO_DATE('12/11/2021','MM/DD/YYYY') then 'Wk 49'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('12/12/2021','MM/DD/YYYY') and TO_DATE('12/18/2021','MM/DD/YYYY') then 'Wk 50'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('12/19/2021','MM/DD/YYYY') and TO_DATE('12/25/2021','MM/DD/YYYY') then 'Wk 51'
            when NVL(MCS_SVC_REQ.MSR_CLOSED_WEB_DATE,MCS_SVC_REQ.MSR_NET30_CLOSE_DATE) between TO_DATE('12/26/2021','MM/DD/YYYY') and TO_DATE('12/31/2021','MM/DD/YYYY') then 'Wk 52'
            else 'Updt_Qry'
            end

Upvotes: 1

Views: 107

Answers (1)

OldProgrammer
OldProgrammer

Reputation: 12169

Here is a much quicker way to find a formatting error in a large query as opposed to reviewing a 200-line query. Download Oracle SQL Developer (free), and paste the query in a sql window, try executing it. I pasted your query in, and tried to execute it. Get the error below:

ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"
*Cause:    
*Action:
Error at Line: 77 Column: 118

It is indeed a bad quote character enter image description here

It also does not help that Oracle provides terrible error messages.

Upvotes: 3

Related Questions