Re: Views and columns in views...

eekama_at_unix1.sncc.lsu.edu (Subraya T. Kamath) wrote:
>
> David A Dreyer (dreyerd_at_cps.msu.edu) wrote:
> : Howdy,
 

> : I'm using Oracle-7 with Powerbuilder, and am running into problems
> : with selecting from Views. From the PB Database administrator, I create a
> : View; the View is actually a join across a couple of tables. Then,
> : when I query the View and use a column from the View in a WHERE clause,
> : I get an "Invalid column name" message. What am I doing wrong? Here's
> : an example of the situation:
 

> : CREATE VIEW MyView (Column1, Column2, Column3) AS
> : SELECT TableA.Column1, TableA.Column2, TableB.Column3
> : FROM TableA, TableB
> : WHERE TableA.JoinCol = TableB.JoinCol
 

> : Then, selecting from this view as follows:
 

> : SELECT *
> : FROM MyView
 

> : works successfully, but
 

> : SELECT *
> : FROM MyView
> : WHERE Column1="somevalue"
 

> : returns with an "Invalid Column Name" message.
 

> : Any idea what's wrong here, or is this a limitation of Oracle's implementation
> : of Views?
 

> : Thanks for any responses,
 

> : Dave
>
> Yes, it is a limitation.Oracle7 does'nt allow you to manipulate data using views which have a defining query with a group operator or a join(as in u'r view).The reason for this is that it is not clear in these cases for the server to map the data manipulation operation back to the base table.
>
> kamath.
> --
> ***************************************************************************
> *----- Kamath -----
> ***************************************************************************

No it's not, it's because of the double quotes. Don't do:

select * from T where C = "SomeValue"

Do:

select * from T where C = 'SomeValue'

According to ansi, "SomeValue" is an identifier (eg: it would be a column name in this context). 'SomeValue' is a character string constant.

This was a query, had nothing to do with a limitation of views.

Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com

ps: through a line feed or two in your comments, not all news readers wrap lines. Received on Fri Mar 31 1995 - 00:00:00 CEST