Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00904 on selecting by column name
ORA-00904 on selecting by column name [message #673103] Tue, 06 November 2018 01:20 Go to next message
chat2raj.s
Messages: 115
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 #673104 is a reply to message #673103] Tue, 06 November 2018 01:48 Go to previous messageGo to next message
John Watson
Messages: 7659
Registered: January 2010
Location: Global Village
Senior Member
You have created the materialized view container table with the column name in lower case. You can rename it with

alter table OV_MIS_OFF_CURR_STK_MV rename column "c_comp" to c_comp;
Re: ORA-00904 on selecting by column name [message #673106 is a reply to message #673104] Tue, 06 November 2018 03:31 Go to previous messageGo to next message
cookiemonster
Messages: 13330
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.
Re: ORA-00904 on selecting by column name [message #673107 is a reply to message #673104] Tue, 06 November 2018 04:33 Go to previous messageGo to next message
chat2raj.s
Messages: 115
Registered: October 2010
Location: Chennai, India
Senior Member
I recreated the base view without those Quote marks " in the column names and it worked fine then.
Re: ORA-00904 on selecting by column name [message #673108 is a reply to message #673107] Tue, 06 November 2018 04:35 Go to previous message
chat2raj.s
Messages: 115
Registered: October 2010
Location: Chennai, India
Senior Member
Thanks Cookiemonster and John Watson.
Previous Topic: PL/SQL error Ora-06502
Next Topic: List of Months between 2 dates
Goto Forum:
  


Current Time: Tue Nov 13 22:42:46 CST 2018