Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Getting "Invalid column name" on select statement from view for no apparent reason
Hi all,
I have a view in my 8i database that I'm having trouble retrieving info for my external reports application. I've traced the problem to a SQL-level issue.
I can select * from my view with no trouble. I can describe this view in SQL+ and it returns the column names/datatypes as expected. DBA studio's Schema mgr shows the view as valid. I can recompile it in Schema without error. I can recreate it in SQL+ without error.
If I try to make any field-specific selection, the query yields an "Invalid Column Name" error. The especially strange thing to me is that the line# of the invalid column name is the view name itself: i.e. in the example:
SQL> Select
2 column1, 3 column2, 4 column3,
The error I receive is:
ERROR at line 5:
ORA-00904: invalid column name
I do use column aliases in my view and I have tried creating them with and without double quotes; i.e. this way:
select
table1.column_name as ALIAS, table1.column_2 as ALIAS2, (etc.)
and this way:
select
table1.column_name "ALIAS", table1.column_2 "ALIAS2", (etc.)
Neither method makes a difference in retrieving data from the view. In either case, I can select * but cannot select by specific field name(s).
I also noted that a few of my text fields have aliases longer then the actual field's dimension, because they are selecting from small-dimensioned fields to begin with, or else because they are the result of a decode statement that yields one character (for example 'T' or 'F' as a true/false flag.) I noticed that I couldn't use these fields in the WHERE or ORDER BY clause of a query from this view.
I thought this field-name truncation might be the problem, so I made modifications to the view: I either chose shorter alias names or generated longer output (by padding my short DECODE outputs with spaces, i.e. instead of 'T', I generate ' T' for example, and then I plan on parsing it out downstream in my report application.) This resulted in a view with all field names short enough to display completely in the header without truncation. After doing this, I was able to use specific field names in the WHERE and ORDER BY clauses when selecting from the view. However, I still cannot select specific fields, only select *.
BTW, it does not matter which field I select or combination of fields. The view will not accept any query which refers to fields by name. Only select * works.
This will not work for my external report application which requires field names to be explicitly specified.
Any thoughts? Received on Thu Nov 15 2001 - 13:10:00 CST