Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Create a view of all columns in join except join condition

122922May 27 2005 — edited May 28 2005
Is there a way I can create a view that includes all uniquely named columns across two tables, without enumerating what those columns are?

e.g.
create Table X
(id number,
x1 number,
x2 number,
x3 number);

create Table Y
(id number,
y1 number,
y2 number,
y3 number);

I want to create view Z with columns all the uniquely named columns, plus the "id" column which joins X and Y:

id, x1, x2, x3, y1, y2, y3

The trick is that X and Y will, over time, have new columns added to them (x4, x5..., y4, y5...), and I'd like to be able to have a process that can redefine Z to include the new columns automatically whenever X or Y changes.

The following doesn't work because it will cause a column name conflict between x.id and y.id:

create view Z as
select x.*, y.*

from x, y
where x.id = y.id

What I want is something equivalent to this pseudo-SQL to get around the duplication of the id column in the select:

create view Z as
select x.*, y.* except y.id
from x, y
where x.id = y.id

The new columns added to X and Y will be uniquely named, so I don't have to worry about conflicts in that part of the process -- but I don't know what those new column names will be ahead of time. What I'd like to find is a way for all columns except those involved in the join to be specified in the view.

Any ideas?

Thanks.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 25 2005
Added on May 27 2005
4 comments
401 views