Thanks for the question, Mauro.
Asked: December 15, 2016 - 8:01 am UTC
Last updated: December 16, 2016 - 2:36 am UTC
Version: 11.2.0.4
Viewed 1000+ times
You Asked
Oracle 11.2.0.4
Test case:
create table t1 ( a number, x number, y number );
create table t2 ( b number, x number );
create table t3 ( c number, y number, z number );
create table t4 ( d number, z number );
This query works:
SELECT *
FROM t1
JOIN t2 USING (x)
JOIN t3 USING (y)
JOIN t4 USING (z)
This one does not:
SELECT *
FROM t1
JOIN t2 USING (x)
JOIN t4 USING (z)
JOIN t3 USING (y)
I was unable to find an explanation of the reason in Oracle manuals, but I think there is one somewhere.
What am I missing?
Have a nice day and thank you for your time
Mauro
and Connor said...
Because you are trying to get from T2 => T4 using a column called Z.
If we just extract *that* part out, the error becomes more obvious:
SQL> SELECT *
2 FROM t2
3 JOIN t4 USING (z);
JOIN t4 USING (z)
*
ERROR at line 3:
ORA-00904: "T2"."Z": invalid identifier
There is no column Z in T2. In the full query, because some query transformation is taking place before your query is executed, the error looks a little more cryptic
SQL> SELECT *
2 FROM t1
3 JOIN t2 USING (x)
4 JOIN t4 USING (z)
5 JOIN t3 USING (y);
JOIN t4 USING (z)
*
ERROR at line 4:
ORA-00904: "from$_subquery$_003"."Z": invalid identifier
but its the same thing.