Re: view problem

A copy of this was sent to daud11_at_hotmail.com (if that email address didn't require changing) On Fri, 10 Sep 1999 12:58:07 GMT, you wrote:

>Hi
>
>I created a view based on a few tables and i renamed the column names
>in my view.
>table_a
>col1_a
>col2_a
>
>table_b
>col1_b
>col2_b
>
>create view my_view as
>select col1_A "new_col1", col1_B "new_col2"
>from table_a , table_b;
>
>I found that I am not able to:
>
>select * from my_view where new_col2 = "something";
>
>It says invalid column name. Why?
>
>Regards
>Daud
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

double quotes are quoted identifiers.

single quotes are for character string constants.

In the view creation you have:

>create view my_view as
>select col1_A "new_col1", col1_B "new_col2"
>from table_a , table_b;

that created a view with 2 columns both of which are in LOWER CASE now.

You would have to forever issue:

select "new_col1", "new_col2" from my_view

and never

select new_col1, new_col2 from my_view.

In the second one where you select there are 2 errors. you have:

select * from my_view where new_col2 = "something";

error 1 - there is no column new_col2 in the view, only "new_col2"

error 2 - there is no column "something" in the view you probably meant 'something' -- a character string constant.

Quoted identifiers allow you to have special characters in your identifiers. It is possible for example to:

tkyte_at_8.0> create table "*" ( "*" int, "=" int );

Table created.

tkyte_at_8.0> desc "*"

 Name                            Null?    Type
 ------------------------------- -------- ----
 *                                        NUMBER(38)
 =                                        NUMBER(38)

tkyte_at_8.0> select 5+"="+2, "*" from "*";

looks strange but its OK.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Sep 10 1999 - 08:17:18 CDT