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

Its Oracle 9.2.0.4.

I rewrote the query as:

select count(1)
from mtl_material_transactions m
, (select *

from po_headers_all ph
, po_lines_all pl

where ph.po_header_id = pl.po_header_id) phpl
, 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 r.po_line_id = phpl.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(+)

so that 'phpl' is an inline view rather than 2 seprate tables

And I get the same result ... this runs fine but the ANSI standard implementation fails.

Removing po_headers_all takes care of the query. If I remove po_headers_all it works fine. Received on Sun Aug 21 2005 - 01:15:48 CDT