Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Getting "Invalid column name" on select statement from view for no apparent reason

Getting "Invalid column name" on select statement from view for no apparent reason

From: j.e.p. <joep_at_automedrx.com>
Date: 15 Nov 2001 11:10:00 -0800
Message-ID: <fb7e6a46.0111151110.276a9a90@posting.google.com>


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,

  5 from my_view;

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US