Re: Determine source-table from field within join?

On Aug 13, 9:19 am, Steve Howard <stevedhow..._at_gmail.com> wrote:
> On Aug 13, 3:49 am, dean <deanbrow..._at_yahoo.com> wrote:
>
> > 10g.
>
> > How can one tell which table a field in a query comes from? For
> > example a join between tables A and B contains 10 fields (F1 to F10),
> > 5 from A and 5 from B. How would one tell if field F1 is from A or B?
> > Is there a SQL-parsing system view?
>
> A query can't be parsed if the same column exists in two sources,
> without an alias...
>
> SQL> create table t0813a(a number, b number);
>
> Table created.
>
> SQL> create table t0813b(a number, b number);
>
> Table created.
>
> SQL> insert into t0813a values(1,2);
>
> 1 row created.
>
> SQL> insert into t0813b values(1,3);
>
> 1 row created.
>
> SQL> commit;
>
> Commit complete.
>
> SQL> select t0813a.a,b
> 2 from t0813a, t0813b
> 3 where t0813a.a=t0813b.a;
> select t0813a.a,b
> *
> ERROR at line 1:
> ORA-00918: column ambiguously defined
>
> SQL>
>
> If you have views on which you are querying that have the alias
> defined in them, you may have to "walk the tree" to get the column in
> the "root" SQL.

SQL> create table t0813b(a number, b number); Table created.

SQL> create table t0813b(a number, b number); Table created.

SQL> insert into t0813a values(1,2);
1 row created.

SQL> insert into t0813b values(1,3);
1 row created.

SQL> select a, t0813a.b
from t0813a inner join t0813b using (a);

         A B
---------- ----------

         1 2

1 row selected.

In this case (with the USING clause), you CANNOT specify the table name.

In this case also, it would be impossible to determine the table name from the query. Received on Mon Aug 13 2007 - 10:07:32 CDT