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

1.In your example, line 4 has an extra comma and the sql would be invalid, assume this is just a typo.
2. I was having erratic ORA-904's and it ended up being an oracle bug addressed in an 8.1.7 patch. However I was using pl/sql. The problem was related to role based security. It could be worked around without the patch via explicit grants.

Just a suggestion, if you can make a view accessing the standard Oracle emp and dept tables it will be easier for people to mimic your question on their own machines or local sites.

joep_at_automedrx.com (j.e.p.) wrote in message news:<fb7e6a46.0111151110.276a9a90_at_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 - 21:31:53 CST