CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Rasmi
Rasmi

Reputation: 509

ORA-00904: "ROW_ID": invalid identifier

I wanted to get the rowId from the below query, but when I execute the query I get an error.

WITH DATA_TBL AS
                (SELECT 
          /*+ parallel(scr,10)
              parallel(el,10)  */ scr.rowid  AS "row_id", scr.*
          FROM SOURCE_TABLE scr
          LEFT OUTER JOIN CITY el ON (el.CITY_NAME  = scr.SUN_W)
          WHERE el.rowid IS NULL
          AND scr.SUN_W IS NOT NULL
          AND GREATEST(scr.SUN_WA, scr.B ) IS NULL
          )
          
          SELECT row_id FROM DATA_TBL;

When I execute the above query I get the below error, I am not sure what I am doing wrong.

ORA-00904: "ROW_ID": invalid identifier
00904. 00000 -  "%s: invalid identifier"

But the query runs without any issue if I do the below, but I wanted to select only row_id.

Upvotes: 0

Views: 1673

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Do not use double quotes for identifiers! By default, Oracle upper cases all identifiers. You have explicitly defined it to be lower-case.

You could say select "row_id", but that is silly. Remove the double quotes in the CTE.

Upvotes: 4

Related Questions