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/D
