Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Determine source-table from field within join?

Re: Determine source-table from field within join?

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


On Aug 13, 11:07 am, dean <deanbrow..._at_yahoo.com> wrote:
> 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.

Well, thank you both for taking me to the woodshed...LOL! Received on Mon Aug 13 2007 - 10:16:19 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US