-
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.)
-
I am your huckleberry
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.
-
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.
Last edited by DaPi; 04-16-2004 at 02:05 PM.
-
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(+);
Last edited by ddrozdov; 04-16-2004 at 05:03 PM.
-
Thank you for the input. I will give it a try when the server comes back up.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|