Re: Obtain underlying table of a select list item per OCI

On Jun 2, 8:38 am, "news.verizon.net" <kenned..._at_verizon.net> wrote:
> "Mathias Waack" <M.Wa..._at_gmx.de> wrote in message
>
> news:4NR0k.512$9W3.42551_at_se2-cb104-9.zrh1.ch.colt.net...
>
>
>
>
>
> > Hi,
>
> > assume a situation where we have two tables:
>
> > create table t1 (c1 number, c2 varchar2(10));
> > create table t2 (c1 number, c2 date);
>
> > and a select like this:
>
> > select t1.c1, t2.c1 from t1, t2;
>
> > This statements comes as an opaque string into an OCI routine which tries
> > to
> > analyze the query. Its easy to obtain column names, datatypes aso from the
> > query, but OCI returns only the short column names. Thus the select list
> > from the query above becomes (c1,c1). Is there a way to distinguish both
> > columns within OCI?
>
> > I'm currently extending an application which generates queries which joins
> > tables containing columns with the same name. Would be nice to find out,
> > which column comes from which table.
>
> > Mathias
>
> This is called a natural join and is a very bad idea.  It is relying on the
> same column names in different tables being keys to a join.  There are a lot
> of circumstances where this is not true.  So while you can use a natural
> join it is not advisable to do so.
>
> In the above alias the columns.  Then they will have different names.
> Jim- Hide quoted text -
>
> - Show quoted text -

I see no join conditions in the listed select example:

select t1.c1, t2.c1 from t1, t2;

What that appears to generate is a Cartesian result set and will return mostly nonsensical 'data'. A natural join would be explicitly coded as such using the ANSI syntax:

select * from t1 natural join t2;

and, yes, it relies upon the tables involved having like-named join columns. Using the old-style syntax the query would still require a WHERE clause (which is missing in the example) equating these two likenamed  columns:

select emp.deptno, dept.deptno
from emp, dept
where dept.deptno = emp.deptno;

The difference in results between what was submitted as an example and a true natural join is striking:

SQL> -- Query like the provided example
SQL> select emp.deptno, dept.deptno
  2 from emp, dept;

    DEPTNO DEPTNO
---------- ----------

        20         10
        30         10
        30         10
        20         10
        30         10
        30         10
        10         10
        20         10
        10         10
        30         10
        20         10

    DEPTNO     DEPTNO
---------- ----------
        30         10
        20         10
        10         10
        20         20
        30         20
        30         20
        20         20
        30         20
        30         20
        10         20
        20         20

    DEPTNO     DEPTNO
---------- ----------
        10         20
        30         20
        20         20
        30         20
        20         20
        10         20
        20         30
        30         30
        30         30
        20         30
        30         30

    DEPTNO     DEPTNO
---------- ----------
        30         30
        10         30
        20         30
        10         30
        30         30
        20         30
        30         30
        20         30
        10         30
        20         40
        30         40

    DEPTNO     DEPTNO
---------- ----------
        30         40
        20         40
        30         40
        30         40
        10         40
        20         40
        10         40
        30         40
        20         40
        30         40
        20         40

    DEPTNO     DEPTNO
---------- ----------
        10         40

56 rows selected.

Note there are 56 rows returned, 42 of which are nonsense. Using a true natural join produces usable data, and only one column in the output:

SQL> -- True natural join query
SQL> select deptno
  2 from emp natural join dept;

    DEPTNO

        20
        30
        30
        20
        30
        30
        10
        20
        10
        30
        20

    DEPTNO

        30
        20
        10

14 rows selected.

SQL> Again, I see no 'natural join' in the example provided in the original post. I do see a mess providing no useful information to anyone, presuming the example query is merely a simplified version of the output of the query generator.

David Fitzjarrell Received on Mon Jun 02 2008 - 10:27:48 CDT