Run query activity error

Help Activities
, ,
1

I use run query activity but insert query error as below.

image
image1104×395 45.2 KB

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
2

@fairymemay

change the double quotes in the middle to `
e.g.

"SELECT a.extract_date, a.due_date `Due_Date`, c.I9_company_code `Company Code`, …

2 Likes
3

@fairymemay- you can change the double quotes to single quotes of the query and try.

1 Like
5

now not error.
But if I run it show error as below.

image
image1211×404 43.5 KB

@jack.chan @Gaurav_Malhotra

6

@fairymemay

can you paste the query here??

and try use ’ instead of `

7

@jack.chan

"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"
8

Try replacing single quotes with two double quotes.

Eg “select * from a “””“abc”“”" "

9

@Gaurav_Malhotra Now error as below.

image

code query

"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"
10

Can suggest me for solve it.
Now error

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"
11

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"
3 Likes
12

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.