ANSI joins not working...

Hi All -

I am running Oracle 9i (9.2.0.1.0) on Windows XP (development) and experimenting with the ANSI outer join syntax - and it doesn't seem to be working!

The queries:

select p.PORTFOLIO_CODE, h.HOLDING_NUMBER_SHARES from PORTFOLIO_MASTER p left outer join PORTFOLIO_HOLDINGS h on p.PORTFOLIO_CODE = h.PORTFOLIO_CODE
where h.INSTRUMENT_CODE = 'AFI'

and

select p.PORTFOLIO_CODE, h.HOLDING_NUMBER_SHARES from PORTFOLIO_MASTER p full outer join PORTFOLIO_HOLDINGS h on p.PORTFOLIO_CODE = h.PORTFOLIO_CODE
where h.INSTRUMENT_CODE = 'AFI'

return

PORTFOLIO_CODE	HOLDING_NUMBER_SHARES
814	255000

whereas the old syntax

select p.PORTFOLIO_CODE, h.HOLDING_NUMBER_SHARES from PORTFOLIO_MASTER p , PORTFOLIO_HOLDINGS h where p.PORTFOLIO_CODE = h.PORTFOLIO_CODE(+) and h.INSTRUMENT_CODE(+) = 'AFI'

returns (as expected)

PORTFOLIO_CODE	HOLDING_NUMBER_SHARES
814	255000
18	null
612	null
107	null
337	null
813	null
480	null
483	null
487	null
596	null
462	null
471	null
259	null
134	null
380	null

So why isn't the ANSI syntax working? Does it require some kind of switch or indexes set-up (can't imagine so!).

Thanks,
Peter Received on Tue Jun 14 2005 - 04:40:45 CDT