Skip to main content

Search

Welcome to Tibero GTS!

Error Occurs When mixing Oracle Outer Join and ANSI Join

Document Type | Troubleshooting 

Category | App Development

Applicable Product Versions | 6FS07, 7FS02

Error Code | 8047

Document Number | TDETS009

 

Issue

When mixing Oracle Outer Join and ANSI Join, the error TBR-8047: Outer join operator (+) cannot be used with ANSI joins. occurs.

 

Cause

An error occurs at the syntax check stage when Oracle Outer Join and ANSI Join are mixed. This is because a special processing logic that previously allowed partial mixing has been removed (reverted). 
As a result, queries using both syntaxes together are considered syntax errors and will not execute.

 

Solutions

Modify the query so that Oracle Outer Join and ANSI Join are not mixed.
  • TBR-8047: Outer join operator (+) cannot be used with ANSI joins error is intended behavior, and the two syntaxes cannot be used together.
 
Note
 
Reenact Scenario
 
1. Create TEST TABLE
SQL> create table t128504(c1 number, c2 number);
SQL> create table t1285042(c1 number, c2 number);
SQL> insert into t128504 select level, level+1 from dual connect by level<10;
SQL> insert into t1285042 select level, level+1 from dual connect by level<10;
 
2. Mix Oracle outer join and ANSI join
SQL> select * from t128504 a1 LEFT OUTER JOIN t1285042 a2 on a1.c1 = a2.c1(+);
TBR-8047: Outer join operator (+) cannot be used with ANSI joins.
at line 1, column 16 of null:
select * from t128504 a1 LEFT OUTER JOIN t1285042 a2 on a1.c1 = a2.c1(+)
 
3. Remove mix of Oracle outer join and ANSI join 
SQL> select * from t128504 a1 LEFT OUTER JOIN t1285042 a2 on a1.c1 = a2.c1;

C1 C2 C1 C2
---------- ---------- ---------- ----------
1 2 1 2
2 3 2 3
3 4 3 4
4 5 4 5
5 6 5 6
6 7 6 7
7 8 7 8
8 9 8 9
9 10 9 10
 
 
 

Related to