SELECT a.extract_date ,a.due_date "Due_Date" ,c.l9_company_code "Company_Code" ,COUNT(*) "BCV_Transaction" ,SUM(a.actual_amount) "BCV_Amount" ,(CASE WHEN c.l9_company_code='RF' THEN 'RF' WHEN c.l9_company_code='RM' THEN 'RF' WHEN c.l9_company_code='TS' THEN 'RF' WHEN c.l9_company_code='TI' THEN 'TI' WHEN c.l9_company_code='TD' THEN 'TD' WHEN c.l9_company_code='VC' THEN 'VC' ELSE c.l9_company_code END) AS "Company_Group" FROM AR1_DIRECT_DEBIT_REQUEST a, CSM_ACCOUNT c WHERE a.account_id = c.ban (+) AND a.request_status IN('E') AND a.bank_code ='025' AND c.l9_company_code <> 'KC' AND a.due_date = TO_DATE('20231030','yyyymmdd') GROUP BY a.extract_date,a.due_date,c.l9_company_code
"SELECT a.extract_date ,a.due_date `Due_Date` ,c.l9_company_code `Company_Code` ,COUNT(*) `BCV_Transaction` ,SUM(a.actual_amount) `BCV_Amount` ,(CASE WHEN c.l9_company_code=`RF` THEN `RF` WHEN c.l9_company_code=`RM` THEN `RF` WHEN c.l9_company_code=`TS` THEN `RF` WHEN c.l9_company_code=`TI` THEN `TI` WHEN c.l9_company_code=`TD` THEN `TD` WHEN c.l9_company_code=`VC` THEN `VC` ELSE c.l9_company_code END) AS `Company_Group` FROM AR1_DIRECT_DEBIT_REQUEST a, CSM_ACCOUNT c WHERE a.account_id = c.ban (+) AND a.request_status IN(`E`) AND a.bank_code =`025` AND c.l9_company_code <> `KC` AND a.due_date = TO_DATE(`20231030`,`yyyymmdd`) GROUP BY a.extract_date,a.due_date,c.l9_company_code"
"SELECT a.extract_date ,a.due_date ""Due_Date"" ,c.l9_company_code ""Company_Code"" ,COUNT(*) ""BCV_Transaction"" ,SUM(a.actual_amount) ""BCV_Amount"" ,(CASE WHEN c.l9_company_code=""RF"" THEN ""RF"" WHEN c.l9_company_code=""RM"" THEN ""RF"" WHEN c.l9_company_code=""TS"" THEN ""RF"" WHEN c.l9_company_code=""TI"" THEN ""TI"" WHEN c.l9_company_code=""TD"" THEN ""TD"" WHEN c.l9_company_code=""VC"" THEN ""VC"" ELSE c.l9_company_code END) AS ""Company_Group"" FROM AR1_DIRECT_DEBIT_REQUEST a, CSM_ACCOUNT c WHERE a.account_id = c.ban (+) AND a.request_status IN(""E"") AND a.bank_code =""025"" AND c.l9_company_code <> ""KC"" AND a.due_date = TO_DATE(""20231030"",""yyyymmdd"") GROUP BY a.extract_date,a.due_date,c.l9_company_code"
Run query: ERROR [42S22] [Oracle][ODBC][Ora]ORA-00904: “‘yyyymmdd’”: invalid identifier
Code query as below.
"SELECT a.extract_date ,a.due_date ""Due_Date"" ,c.l9_company_code ""Company_Code"" ,COUNT(*) ""BCV_Transaction"" ,SUM(a.actual_amount) ""BCV_Amount"" ,(CASE WHEN c.l9_company_code=""RF"" THEN ""RF"" WHEN c.l9_company_code=""RM"" THEN ""RF""
WHEN c.l9_company_code=""TS"" THEN ""RF""
WHEN c.l9_company_code=""TI"" THEN ""TI""
WHEN c.l9_company_code=""TD"" THEN ""TD""
WHEN c.l9_company_code=""VC"" THEN ""VC""
ELSE c.l9_company_code END) AS ""Company_Group""
FROM AR1_DIRECT_DEBIT_REQUEST a, CSM_ACCOUNT c
WHERE a.account_id = c.ban (+)
AND a.request_status IN(""E"")
AND a.bank_code =""025"" AND c.l9_company_code <> ""KC""
AND a.due_date = TO_DATE(""'20231030'"",""'yyyymmdd'"")
GROUP BY a.extract_date,a.due_date,c.l9_company_code"
Hey @fairymemay
If I remember correctly Oracle is using the single quotes in string, so it can be cause of your issue.
Try it:
"Select
a.extract_date,
a.due_date ""Due_Date"",
c.l9_company_code ""Company_Code"",
COUNT(*) ""BCV_Transaction"",
SUM(a.actual_amount) ""BCV_Amount"",
(Case
When c.l9_company_code = 'RF' THEN 'RF'
When c.l9_company_code = 'RM' THEN 'RF'
When c.l9_company_code = 'TS' THEN 'RF'
When c.l9_company_code = 'TI' THEN 'TI'
When c.l9_company_code = 'TD' THEN 'TD'
When c.l9_company_code = 'VC' THEN 'VC'
Else c.l9_company_code
End) As ""Company_Group""
From
AR1_DIRECT_DEBIT_REQUEST a,
CSM_ACCOUNT c
Where
a.account_id = c.ban (+)
And a.request_status In ('E')
And a.bank_code = '025'
And c.l9_company_code <> 'KC'
And a.due_date = TO_DATE('20231030','yyyymmdd')
Group By
a.extract_date,
a.due_date,
c.l9_company_code"