Outer join - same query, different results...

From: <rpmfantasy_at_gmail.com>
Date: 18 Aug 2005 13:45:01 -0700
Message-ID: <1124397901.886745.214270@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? Received on Thu Aug 18 2005 - 15:45:01 CDT