Home » SQL & PL/SQL » SQL & PL/SQL » Accessing duplicate field name in a view
Accessing duplicate field name in a view [message #262991] Tue, 28 August 2007 13:39 Go to next message
clintonf
Messages: 82
Registered: May 2006
Member
Hi all,

Is it possible to access a field in a view which exists twice in a view?

For example:

I have two tables, employees and friends:

SQL> SELECT * FROM employees;
emp_id     name
------     -----
1          Joe
2          Mary

SQL> SELECT * FROM friends;
friend_id     name
---------     -----
1          Bill
2          Jim


Now I want to create the following view. Please forgive the somewhat simple (and maybe inappropriate) example but I'm just using it to get my point across.

CREATE VIEW everyone
AS
SELECT E.name, F.name 'buddies' FROM employees E, friends F;


I have to use the 'buddies' field alias or I get an error about duplicate field names.

I am unable to reference the F.name column as 'buddies' - a "does not exist" error is returned.

How can I create/access the view so that I can specify both E.name or F.name as needed (if at all)?

Thanks!
Re: Accessing duplicate field name in a view [message #262993 is a reply to message #262991] Tue, 28 August 2007 13:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
All columns of a table or view must have a different name; otherwise how Oracle will which column you ask when you ask for "name"?

Regards
Michel
Re: Accessing duplicate field name in a view [message #262995 is a reply to message #262993] Tue, 28 August 2007 13:45 Go to previous messageGo to next message
clintonf
Messages: 82
Registered: May 2006
Member
I can create the view OK, supplying an alias to the duplicate column name.

But is it possible to specify it in a WHERE clause?

Thanks!
Re: Accessing duplicate field name in a view [message #262996 is a reply to message #262995] Tue, 28 August 2007 13:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you talk about the view definition, you can't use the aliases in the where clause you have to use the table column names.
If you talk about the use of the view, you must use the alias names, the underlying column names are not visible.

Regards
Michel
Re: Accessing duplicate field name in a view [message #262998 is a reply to message #262996] Tue, 28 August 2007 13:57 Go to previous messageGo to next message
clintonf
Messages: 82
Registered: May 2006
Member
Sorry, I didn't specify.

The problem that I am experiencing is when I am SELECTing from the view.

I am unable to refer to the duplicated field name using the alias ("buddies"). The error that I get is:

ORA-00904: "BUDDIES": invalid identifier


I have checked and rechecked that "buddies" is the alias for that column.

My version of Oracle is 9.2.0.4, if that makes a difference.

Thanks!
Re: Accessing duplicate field name in a view [message #263008 is a reply to message #262991] Tue, 28 August 2007 14:19 Go to previous messageGo to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
  1  CREATE VIEW everyone
  2  AS
  3* SELECT E.name, F.name 'buddies' FROM employees E, friends F
SQL> /
SELECT E.name, F.name 'buddies' FROM employees E, friends F
                      *
ERROR at line 3:
ORA-00923: FROM keyword not found where expected

In your example, I'm assuming you meant double quote (")?

  1  CREATE VIEW everyone
  2  AS
  3* SELECT E.name, F.name "buddies" FROM employees E, friends F
SQL> /

View created.

When you quote column names such as you did in the view, the column must be referenced using quotes, and it is case sensitive
SQL> SELECT buddies FROM everyone;
SELECT buddies FROM everyone
       *
ERROR at line 1:
ORA-00904: "BUDDIES": invalid identifier

  1* SELECT name, "buddies" FROM everyone
SQL> /

NAME                 buddies
-------------------- --------------------
Joe                  Bill
Mary                 Bill
Joe                  Jim
Mary                 Jim

That's why Oracle recommends not to use quotes or ensure the names are in uppercase.
Re: Accessing duplicate field name in a view [message #263017 is a reply to message #263008] Tue, 28 August 2007 14:37 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Just to add: I believe you, actually, didn't want to use ANY quotes, not single ones (so that it represents a string), and not double quotes (to name a column). What you really want is just BUDDIES, with no quotes. It would then be an alias for a column:
SQL> create view everyone
  2  as
  3  select e.ename, d.dname buddies    --> see? no quotes!
  4  from emp e, dept d;

View created.

SQL> select buddies from everyone;      --> no quotes here either

BUDDIES
--------------
ACCOUNTING
ACCOUNTING
<snip>
Re: Accessing duplicate field name in a view [message #263023 is a reply to message #263017] Tue, 28 August 2007 14:57 Go to previous message
clintonf
Messages: 82
Registered: May 2006
Member
yup and yup!

Thanks for clearing that up!
Previous Topic: show index from table in oracle???
Next Topic: PL/SQL - Circular dependencies
Goto Forum:
  


Current Time: Thu Dec 08 06:16:37 CST 2016

Total time taken to generate the page: 0.05823 seconds