Re: Oddness Creating Views?

Andrew,

Thanks there's some good things for me to think about there. Just getting my brain around shifting MS Access to Oracle in a self taught floundering sort of way.

Doug.

"Andrew Simkovsky" <asimkovsky_at_email.msn.com> wrote in message news:OAt4dQsxAHA.83_at_cpmsnbbsa07...
> > 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 - 20:55:24 CDT