Learn the motive and how to unravel the ORA-01416 error message in Oracle.
Description
When you encounter an ORA-01416 error, the following error message will appear:
ORA-01416: two tables cannot be outer-joined to every different
Cause
You tried to be part of two tables, however you carried out an outer join on both tables to each other. This has created a round outer be a part of between the two tables.
Resolution
The option(s) to resolve this Oracle error are:
Option #1
Correct your SQL so that you are now not outer joining both tables to every other. You can solely function an outer join one way.
For example, if you tried to execute the following SQL statement:
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_id
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id(+)
AND suppliers.supplier_name(+) = orders.supplier_name;You would receive the following error message:
You ought to right this SQL statement through putting off the round outer join. For example:
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_id
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id(+)
AND suppliers.supplier_name = orders.supplier_name(+);