ORA 00904 invalid identifier with left/right outer join

2
votes

I have query like:

SELECT *
FROM table1 LEFT OUTER JOIN table2
ON table1.id=table2.c_id

It gives me an error

ORA-00904 "table2.c_id" invalid identifier

The thing is that query works with FULL OUTER JOIN and with INNER JOIN - no error. Even this one works:

SELECT *
FROM table1, table2
WHERE table1.id=table2.c_id(+)

Error only occurs with right or left outer join and only on test db.

Both tables are partitioning.

Production db, where query runs perfect - Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

Test db, where query fails - Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

Configuration of tables on both db are the same.

Any suggestions what could it be?

2
oraclejoinoracle11gpartitioningouter-join
I don't have your tables to test. Please post the create table and insert statements for sample data. Or else, create a SQL Fiddle and post a link – Lalit Kumar B
Have you tried using aliases for table1 and table2, i.e. select * from table1 t1 left outer join table2 t2 on t1.id = t2.c_id ? Is there a database link involved? (Oracle has a long history of broken ANSI Joins when DB links are involved) – Frank Schmitt
@FrankSchmitt yes, I used aliases - no success. Not sure about db links, will check – Tatiana
Some more things to check (all related to Oracle bugs with ANSI syntax): - does one of the tables contain a domain index? - does one of the tables contain a CLOB/BLOB column? - does one of the tables contain a XMLTYPE column? – Frank Schmitt

2 Answers

0
votes

I don't see any issue with the SQL.

On my 12c database, no issues:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create table x(id number);

Table created.

SQL> create table y(c_id number);

Table created.

SQL> insert into x values(1);

1 row created.

SQL> insert into y values(1);

1 row created.

SQL> insert into y values(2);

1 row created.

SQL> SELECT *
  2  FROM y LEFT OUTER JOIN x
  3  ON x.id=y.c_id;

      C_ID         ID
---------- ----------
         1          1
         2

On 11g R2, see this SQL Fiddle.

-1
votes

could you try this

 SELECT table1.* ,table2.*
 FROM table1 LEFT OUTER JOIN table2
 ON table1.id=table2.c_id