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