Home/ Questions/Q 75364
Next
Answered
Question
Garcia Gorry
  • 0
Explorer
Asked: 2021-09-18T12:15:12+03:00 In:

ORA-01417: a table may be outer joined to at most one other table

  • 0

I am working on Oracle database 11g, and facing an issue while trying to execute the following query:

SELECT d.department_name, e.manager_id, l.location_id, sum(e.salary)
FROM departments d, employees e, locations l
WHERE d.department_id = e.department_id
AND e.manager_id = d.manager_id (+)
AND l.location_id = d.location_id (+)
GROUP BY d.department_name, e.manager_id, l.location_id;

the query is not executed and returned the following error :

ERROR at line 4:
ORA-01417: a table may be outer joined to at most one other table

Hopefully somebody help me understanding what is going on, and why the database refuses to execute the query.

thanks in advance.

ORAora-01417
Share

    Related Questions

    1 Answer

    1. Best Answer
      Master Oracle ACE Pro ♠ | Oracle Senior ERP Technical Consultant

      Before Oracle database release 12c, the error ORA-01417 is raised when you have more than one table on the left-hand side of an outer join. To overcome this limit, we can convert the join to an ANSI syntax, e.g.:

      SELECT *
FROM EMPLOYEES E
LEFT JOIN DEPARTMENTS D ON (D.DEPARTMENT_ID = E.DEPARTMENT_ID)
LEFT JOIN LOCATIONS L ON (L.LOCATION_ID = D.LOCATION_ID);

      From the 12c version, Oracle has supported having multiple tables on the left-hand side of the join.

      • 0

    Leave an answer

    You must login to add an answer.

    Need An Account,