CODEWITHSUNDEEP
sqloracle-database
Priyanka Awasare
Priyanka Awasare

Reputation: 5

how can i resolve invalid identifier error

I have run below query

 v_sql2 := 'SELECT SUBSTR((SUM(CASE
                           WHEN srm_bill_date between "01-04-2022" and "01-04-2023" THEN
                            (SUM(srm_bill_amount) / 10000000)
                            END)),
                            0,
                            4) AS FY_Revenue_InCr
              FROM m_servicemaster
             GROUP BY srm_bill_date';

result gives error :

ORA-00904: "01-04-2023": invalid identifier

I need solution for this

Upvotes: 0

Views: 72

Answers (1)

MT0
MT0

Reputation: 167774

Double quotes are for identifiers (i.e. column or table names, etc). Single quotes are for literals. You are using double quotes when you should use single quotes (and you are using strings for dates when you should use date literals).

Since you are using single-quotes inside a string literal then you need to use two single quotes to escape them.

v_sql2 := 'SELECT SUBSTR(
                    SUM(
                      CASE
                      WHEN srm_bill_date between DATE ''2022-04-01''
                                             and DATE ''2023-04-01''
                      THEN SUM(srm_bill_amount) / 10000000
                      END
                    ),
                    0,
                    4
                  ) AS FY_Revenue_InCr
           FROM   m_servicemaster
           GROUP BY srm_bill_date';

or:

v_sql2 := 'SELECT SUBSTR(
                    SUM(
                      CASE
                      WHEN srm_bill_date between TO_DATE( ''01-04-2022'', ''DD-MM-YYYY'')
                                             and TO_DATE( ''01-04-2023'', ''DD-MM-YYYY'')
                      THEN SUM(srm_bill_amount) / 10000000
                      END
                    ),
                    0,
                    4
                  ) AS FY_Revenue_InCr
           FROM   m_servicemaster
           GROUP BY srm_bill_date';

It is also odd that you are using SUM twice; it is not invalid but it may not be what you want as it makes the GROUP BY clause seemingly irrelevant.

I would have written the query as:

v_sql2 := 'SELECT SUBSTR(SUM(srm_bill_amount / 10000000), 0, 4) AS FY_Revenue_InCr
           FROM   m_servicemaster
           WHERE  srm_bill_date >= DATE ''2022-04-01''
           AND    srm_bill_date <  DATE ''2023-04-01'';

Upvotes: 1

Related Questions