Outer join error

I have this statement in a view and want to put outer join on the weakness table.
How can I resolve this error ORA-01417

Thanks in advance!

SELECT
p.poam_id,
w.weakness_id,
w.weakness_desc,
w.poc,
w.total_resources_required,
w.scheduled_completion_date,
m.milestone_desc,
m.scheduled_completion_date,
mcl.change_log,
rwst.weakness_source_type_desc,
rwms.weakness_milestone_status_desc,
w.omit_from_omb
FROM
poam p, weakness w, milestone m, milestone_change_log mcl,
weakness_source ws, ref_weakness_source_type rwst, ref_weakness_milestone_status rwms
WHERE
p.poam_id = w.poam_id (+) AND
w.weakness_id (+) = m.weakness_id AND
ws.weakness_id = w.weakness_id (+) AND
ws.weakness_source_type_code = rwst.weakness_source_type_code AND
m.milestone_id = mcl.milestone_id AND
w.weakness_status_code (+) = rwms.weakness_milestone_status_code;

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

I would strongly suggest you draw a data model (entity-relationship or whatever) for these tables. I suspect you have started off on the wrong foot and a diagram should help you clarify the situation. Then start to put it together one table at a time.

(Check that you have the (+) on the correct side of the conditions. Some of your conditions look suspect to me, though it's hard to tell without knowing your application.)

Inline view is what you do to avoid this error.

SELECT
pw.poam_id,
pw.weakness_id,
pw.weakness_desc,
pw.poc,
pw.total_resources_required,
pw.scheduled_completion_date,
m.milestone_desc,
m.scheduled_completion_date,
mcl.change_log,
rwst.weakness_source_type_desc,
rwms.weakness_milestone_status_desc,
pw.omit_from_omb
FROM
(select p.poam_id, w.weakness_id, w.weakness_desc, w.poc,
w.total_resources_required, w.scheduled_completion_date
w.omit_from_omb
from poam p, weakness w
where p.poam_id = w.poam_id(+)) pw
milestone m,
milestone_change_log mcl,
weakness_source ws,
ref_weakness_source_type rwst,
ref_weakness_milestone_status rwms
WHERE
pw.weakness_id(+) = m.weakness_id AND
pw.weakness_id(+) = ws.weakness_id AND
pw.weakness_status_code(+) = rwms.weakness_milestone_status_code AND
ws.weakness_source_type_code = rwst.weakness_source_type_code AND
m.milestone_id = mcl.milestone_id

;)

DaPi, I had to assume his is right. What else there's to do.
poam_id must realy be a poem_id, right cchiara?

I love outer joins, they are so complicated. :D

Thanks for the suggestion. I tried executing the query but still got same error. Don't quite get it :(


SELECT
pw.poam_id,
pw.weakness_id,
pw.weakness_desc,
pw.poc,
pw.total_resources_required,
pw.scheduled_completion_date,
m.milestone_desc,
m.scheduled_completion_date,
mcl.change_log,
rwst.weakness_source_type_desc,
rwms.weakness_milestone_status_desc,
pw.omit_from_omb
FROM
(select p.poam_id, w.weakness_id, w.weakness_status_code, w.weakness_desc, w.poc,
w.total_resources_required, w.scheduled_completion_date,
w.omit_from_omb
from poam p, weakness w
where p.poam_id = w.poam_id(+)) pw,
milestone m,
milestone_change_log mcl,
weakness_source ws,
ref_weakness_source_type rwst,
ref_weakness_milestone_status rwms
WHERE
pw.weakness_id(+) = m.weakness_id AND
pw.weakness_id(+) = ws.weakness_id AND
pw.weakness_status_code(+) = rwms.weakness_milestone_status_code AND
ws.weakness_source_type_code = rwst.weakness_source_type_code AND
m.milestone_id = mcl.milestone_id;

Follow DaPi's suggestion.
The (+) goes on the deficient side.
If you have to outer join to more than one table use inline views.

I think there is something fundamentally wrong. The only relationship between poam and milestone I can see is this:

Code:

---

p.poam_id = w.poam_id (+) AND
w.weakness_id (+) = m.weakness_id

---

Which, if it were permitted and meant something, would:

EITHER produce a Cartesian product - all rows of poam would match all rows of milestone via the NULL rows of weakness in the outer join (allowing NULL=NULL to be true)

OR lose all the rows where there are none corresponding in weakness (allowing NULL=NULL to be false), in which case you don't have an outer join.


What I have just written boarders on the incomprehensible - you need a picture. I say again: LOOK TO THE DATA MODEL.

The query is trying to outerjoin a table/inline view against 2 or more tables. That is not permitted in Oracle.

Follow DaPi's suggestion. Maybe you don't need all the outer joins.

This is how you avoid the outer join error
the weakness table w is by itself, all other tables are in an
inline view ot. Now oracle thinks you are outer joining one table to another (one) table. This is what I wanted to acheive in the first answer just to get you through ORA-01417
You are going have to add some joins in an inline view. You have 6 tables and only 3 are joined. If the one below runs it will cartesian if you don't add more joins


SELECT
ot.poam_id,
w.weakness_id,
w.weakness_desc,
w.poc,
w.total_resources_required,
w.scheduled_completion_date,
ot.milestone_desc,
ot.scheduled_completion_date,
ot.change_log,
ot.weakness_source_type_desc,
ot.weakness_milestone_status_desc,
w.omit_from_omb
FROM
(select p.poam_id, m.milestone_desc, m.scheduled_completion_date, mcl.change_log,
rwst.weakness_source_type_desc, rwms.weakness_milestone_status_desc,
from
poam p, milestone m, milestone_change_log mcl,
weakness_source ws, ref_weakness_source_type rwst,
ref_weakness_milestone_status rwms
where
ws.weakness_source_type_code = rwst.weakness_source_type_code AND
m.milestone_id = mcl.milestone_id) ot,
weakness w
WHERE
ot.poam_id = w.poam_id(+) AND
ot.weakness_id = w.weakness_id(+) AND
ot.weakness_milestone_status_code = w.weakness_status_code(+);

Thank you for the input. I will give it a try when the server comes back up.

Dmitry, you support my point above. That potentially MASSIVE Cartesian product in the in-line view must suggest that the query is ill-conceived in the first place. So don't tinker with it - RETHINK IT.