Re: Determine source-table from field within join?

From: Steve Howard <stevedhoward_at_gmail.com>
Date: Mon, 13 Aug 2007 13:19:07 -0000
Message-ID: <1187011147.221853.291940@j4g2000prf.googlegroups.com>

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. Received on Mon Aug 13 2007 - 08:19:07 CDT