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

"Mathias Waack" <M.Waack (AT) gmx (DOT) de> wrote

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

"Mathias Waack" <M.Waack (AT) gmx (DOT) de> wrote

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

"Mathias Waack" <M.Waack (AT) gmx (DOT) de> wrote

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

"Mathias Waack" <M.Waack (AT) gmx (DOT) de> wrote

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

On Jun 2, 8:38*am, "news.verizon.net" <kenned... (AT) verizon (DOT) net> wrote:
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 like-
named 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

On Jun 2, 8:38*am, "news.verizon.net" <kenned... (AT) verizon (DOT) net> wrote:
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 like-
named 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

On Jun 2, 8:38*am, "news.verizon.net" <kenned... (AT) verizon (DOT) net> wrote:
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 like-
named 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

On Jun 2, 8:38*am, "news.verizon.net" <kenned... (AT) verizon (DOT) net> wrote:
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 like-
named 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