Column exists but still get this error ORA-00904: invalid identifier
13,957
Shortly speaking - Oracle is case sensitive...
... probably during table creation column was typed UPPERCASE in quotation marks like that:
"DAYTIME"
and in your sql i see this column in lowercase
so you should verify your column name and best change it to version without quotation marks.
Other option is to call this column like that:
= r_scv.DAYTIME
Related videos on Youtube
Author by
user3541984
Updated on June 04, 2022Comments
-
user3541984 almost 2 years
Anyone can help me with this issue
declare lv2_sql VARCHAR2(32767); cursor c_scv is select financial_code, object_id, daytime from stream_category_version; begin for r_scv in c_scv LOOP IF r_scv.financial_code = 'PURCHASE' THEN lv2_sql := 'UPDATE stream_category_version ' || CHR(10) || 'set REVN_PURCHASES_IND = ''Y'', last_updated_by = nvl(last_updated_by, created_by) ' || CHR(10) || 'WHERE object_id = r_scv.object_id AND daytime = r_scv.daytime'; ecdp_dynsql.execute_statement(lv2_sql); ELSIF r_scv.financial_code = 'SALE' THEN lv2_sql := 'UPDATE stream_category_version ' || CHR(10) || 'set REVN_SALES_IND = ''Y'', last_updated_by = nvl(last_updated_by, created_by) ' || CHR(10) || 'WHERE object_id = r_scv.object_id AND daytime = r_scv.daytime'; ecdp_dynsql.execute_statement(lv2_sql); END IF; END LOOP; end;
I have code as shown above, but i got error saying 'ORA-00904: R_SCV.DAYTIME: invalid identifier'. I have checked the table definition for 'stream_category_version' and found the column DAYTIME as shown below
SQL> desc stream_category_version Name Type Nullable Default Comments ------------------ -------------- -------- ------- -------- OBJECT_ID VARCHAR2(32) DAYTIME DATE END_DATE DATE Y NAME VARCHAR2(240) Y FINANCIAL_CODE VARCHAR2(32) Y SORT_ORDER NUMBER Y COMMENTS VARCHAR2(2000) Y
Then i am confused with the error. Anyone can help me ?
Thanks in advance.