Execute query: ERROR [42S22] [Oracle][ODBC][Ora]ORA-00904: "yyyymmdd": invalid identifier

Help Activities
, ,
1

I execute run query but show error as below.

: ERROR [42S22] [Oracle][ODBC][Ora]ORA-00904: “yyyymmdd”: invalid identifier

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

Please guide me for solve it.
Thank you

2

Hi @fairymemay

Here is the query, try this one

SELECT
    a.extract_date,
    a.due_date AS "Due_Date",
    c.l9_company_code AS "Company_Code",
    COUNT(*) AS "BCV_Transaction",
    SUM(a.actual_amount) AS "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
LEFT JOIN
    CSM_ACCOUNT c ON a.account_id = c.ban
WHERE
    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;

I have changed this line in the query,

AND a.due_date = TO_DATE('20231030', 'YYYYMMDD')

Hope it helps!!

3

@mkankatala I run with run query activity from ypur code.

But show error as below.

image
image1136×610 70.8 KB

4

Copy the error and share with me @fairymemay

5

@mkankatala error as attached.

output.txt (6.7 KB)

6

I’ll try to resolve those errors, give me sometime. @fairymemay

7

@mkankatala Thank you.
I will waiting.

8

@mkankatala now O edit code as below.

"Select
    a.extract_date,
    a.due_date As 'Due_Date',
    c.l9_company_code As 'Company_Code',
    COUNT(*) As 'BCV_Transaction',
    SUM(a.actual_amount) As '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('20240112', 'yyyymmdd')
Group By
    a.extract_date, a.due_date, c.l9_company_code"

But output after run activity error
Run query: ERROR [HY000] [Oracle][ODBC][Ora]ORA-00923: FROM keyword not found where expected

9

Hi @fairymemay

Could you try this code:

SELECT
    a.extract_date,
    a.due_date AS "Due_Date",
    c.l9_company_code AS "Company_Code",
    COUNT(*) AS "BCV_Transaction",
    SUM(a.actual_amount) AS "BCV_Amount",
    CASE
        WHEN c.l9_company_code IN ('RF', 'RM', '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
LEFT JOIN
    CSM_ACCOUNT c ON a.account_id = c.ban
WHERE
    a.request_status IN ('E')
    AND a.bank_code = '025'
    AND c.l9_company_code <> 'KC'
    AND a.due_date = TO_DATE('20240112', 'yyyymmdd')
GROUP BY
    a.extract_date, a.due_date, c.l9_company_code

Hope it helps!!

1 Like
10

@mkankatala I copy your code in activity run query (oracle)

It error as below.

image
image1063×592 73.9 KB

11

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