Outer join error

[cchiara]

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

[DaPi]

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.)

[ddrozdov]

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

[ddrozdov]

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.

[cchiara]

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;

[ddrozdov]

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

[DaPi]

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

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.

[oracle_faq]

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.

[ddrozdov]

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(+);

[cchiara]

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