Re: Outer join - same query, different results...

<rpmfantasy_at_gmail.com> wrote in message news:1124397901.886745.214270_at_g43g2000cwa.googlegroups.com...
>I have a query such as:
>
> select *
> from mtl_material_transactions m
> , po_headers_all ph
> , po_lines_all pl
> , mtl_unit_transactions mu
> , mtl_generic_dispositions g
> , wip_entities w
> , oe_order_lines_all o
> , rcv_transactions r
> where
> m.SOURCE_LINE_ID = r.transaction_id(+)
> and ph.PO_HEADER_ID = pl.PO_HEADER_ID
> and r.po_line_id = pl.PO_LINE_ID(+)
> and m.ORGANIZATION_ID = w.organization_id(+)
> and m.TRANSACTION_SOURCE_ID = w.wip_entity_id(+)
> and m.SOURCE_LINE_ID = o.line_id(+)
> and m.TRANSACTION_ID = mu.transaction_id(+)
> and m.ORGANIZATION_ID = g.organization_id (+)
> and m.TRANSACTION_SOURCE_ID = g.disposition_id(+)
>
> This query runs fine.
>
> But the same query (I think) written as:
>
> select *
> from
> mtl_material_transactions m
> left outer join rcv_transactions r
> on m.SOURCE_LINE_ID = r.transaction_id
> left outer join (select * from po_lines_all pl join po_headers_all ph
> on pl.PO_HEADER_ID = ph.po_header_id) phpl
> on r.po_line_id = phpl.po_line_id
> left outer join wip_entities w
> on m.ORGANIZATION_ID = w.organization_id and m.TRANSACTION_SOURCE_ID
> = w.wip_entity_id
> left outer join mtl_unit_transactions mu
> on m.TRANSACTION_ID = mu.transaction_id
> left outer join oe_order_lines_all o
> on m.SOURCE_LINE_ID = o.line_id
> left outer join mtl_generic_dispositions g
> on m.ORGANIZATION_ID = g.organization_id and m.TRANSACTION_SOURCE_ID
> = g.disposition_id
>
> gives me
> ORA-01445: cannot select ROWID from a join view without a key-preserved
> table
>
> What gives?
>

What version of Oracle ?

I think you have to live with the fact that the implementation of the ANSI join syntax is not 100% perfect yet. (9.2.0.1 was particularly bad)

As written, your two queries are not identical, because of the line:

    > and ph.PO_HEADER_ID = pl.PO_HEADER_ID which effectively turns the join
> and r.po_line_id = pl.PO_LINE_ID(+)

into an inner join

My guess is that the crash is occuring on the outer join to the inline view in the ANSI version, you could test by removing the references to po_headrs_all temporarily.

You might also try to list column names explicitly in the inline join, and/or in the main select, in case the internal rewrite is confusing columns.

-- 
Regards

Jonathan Lewis

Now waiting on the publishers:    Cost Based Oracle - Volume 1

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 8th July 2005