ORA-01468: a predicate may reference only one outer-joined table

ORA-01468: a predicate may reference only one outer-joined table

Oracle PL/SQL error message: ORA-01468: a predicate may reference only one outer-joined table.

Cause:

A predicate in the WHERE clause has two columns from different tables with “(+)”.

Solution:

Change the WHERE clause so that each predicate has a maximum of one outer-join table.




Students table

STUDENT_ID FIRST_NAME LAST_NAME
1 Daniel SCOTT
2 Anthony SIMMONS
3 Sophia THOMPSON
4 Emily PETERSON
5 David DAWSON
6 Gabriel LEWIS

Orders table

ORDER_ID COURSE_ID STUDENT_ID
1 1 6
3 4 5
21 5 6
22 1 4

Example:

select s.student_id, s.first_name, s.last_name 
from students s, orders o 
where s.student_id(+) = o.student_id(+)
order by s.student_id;

Output:

ORA-01468: a predicate may reference only one outer-joined table

Correct:

select s.student_id, s.first_name, s.last_name 
from students s, orders o 
where s.student_id = o.student_id(+)
order by s.student_id;

Output:

STUDENT_ID FIRST_NAME LAST_NAME
1 Daniel SCOTT
2 Anthony SIMMONS
3 Sophia THOMPSON
4 Emily PETERSON
5 David DAWSON
6 Gabriel LEWIS