Re: Oddness Creating Views?

> How come I can do this:
>
> select * from pdtable_12_1, pdtable_12_99
>
> Which returns all the rows in all the tables between 1 & 99 in the
> sequence.
>
> But I cannot do this:
>
> create view pdtable_12_All as
> select * from pdtable_12_1, pdtable_12_99
>
> which gives the error: "ORA-00957 duplicate column name" and points at
> the
> '*'

When you are just running the query, you are getting back all the results, but the structure is not stored anywhere. When you create a view, the structure is stored, and it has to follow the rules of a table, meaning no duplicate column names. Obviously the pdtable_12_1 and pdtable_12_99 both have column names a that are the same. By the way, the queries you are showing are Cartesian Products, which should be avoid at all costs. You need to have a WHERE clause in there to do a proper join.

> Further I can do this:
>
> create view pdtable_12_All as
> select unit_code from pdtable_12_1
>
> But cannot do this:
>
> create view pdtable_12_All as
> select unit_code from pdtable_12_1, pdtable_12_99
>
> which gives the error: "ORA-00918 column ambiguously defined" and
> points at
> 'unit_code' (the column name).

In the first statement, you are selecting a column from a single table. In the first statement, you specify a column, but both tables have a column called unit_code, so it doesn't know which one you want. You need to specify it by using table.column_name notation (or use table aliases) like this:

create view pdtable_12_All as
select a.unit_code from pdtable_12_1 a, pdtable_12_99 b

Again, you need a WHERE clause in this to avoid the Cartesian Product.

Andrew Simkovsky
OCP DBA Received on Mon Apr 16 2001 - 17:33:05 CDT