Create a view of all columns in join except join condition
122922May 27 2005 — edited May 28 2005Is 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.