Outer join error
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Outer join error

  1. #1
    Join Date
    Feb 2002
    Posts
    48

    Lightbulb 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

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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.)

  3. #3
    Join Date
    Apr 2004
    Location
    Boston MA
    Posts
    90

    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


  4. #4
    Join Date
    Apr 2004
    Location
    Boston MA
    Posts
    90
    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.

  5. #5
    Join Date
    Feb 2002
    Posts
    48
    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;

  6. #6
    Join Date
    Apr 2004
    Location
    Boston MA
    Posts
    90
    Follow DaPi's suggestion.
    The (+) goes on the deficient side.
    If you have to outer join to more than one table use inline views.

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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 03:05 PM.

  8. #8
    Join Date
    Feb 2004
    Posts
    77
    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.

  9. #9
    Join Date
    Apr 2004
    Location
    Boston MA
    Posts
    90
    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 06:03 PM.

  10. #10
    Join Date
    Feb 2002
    Posts
    48
    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
  •  


Click Here to Expand Forum to Full Width