ORA-00904 on selecting by column name [message #673103] |
Tue, 06 November 2018 01:20 |
chat2raj.s
Messages: 163 Registered: October 2010 Location: Chennai, India
|
Senior Member |
|
|
Why does the below sql gives me an Invalid Identifier error while selecting from a materialized view while the other one is working fine. I have many other materialized views and they are working fine.
SQL> SELECT c_comp FROM OV_MIS_OFF_CURR_STK_MV;
SELECT c_comp FROM OV_MIS_OFF_CURR_STK_MV
ERROR at line 1:
ORA-00904: "C_COMP": invalid identifier
SQL> SELECT "c_comp" FROM OV_MIS_OFF_CURR_STK_MV WHERE ROWNUM = 1;
c_comp
------
01
|
|
|
|
Re: ORA-00904 on selecting by column name [message #673106 is a reply to message #673104] |
Tue, 06 November 2018 03:31 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
You created it that way by wrapping the column name in double quotes.
Anything not wrapped in double quotes is automatically upper-cased and stored in the data dictionary like that and compared to the data dictionary like that.
If you wrap it in double quotes then you have to make then you have to write all references to that item (column/table/view/etc) in the exact same case (if you used mixed then you have to use mixed) and wrapped in double-quotes, unless you created it in all upper case anyway.
SQL> create table bob (a number);
Table created
SQL> select a from bob;
A
----------
SQL> select A from bob;
A
----------
SQL> create table bob2 ("a" number);
Table created
SQL> select a from bob2;
select a from bob2
ORA-00904: "A": invalid identifier
SQL> select A from bob2;
select A from bob2
ORA-00904: "A": invalid identifier
SQL> select "A" from bob2;
select "A" from bob2
ORA-00904: "A": invalid identifier
SQL> select "a" from bob2;
a
----------
SQL>
Never use double quotes in oracle.
|
|
|
|
|