Re: ANSI joins not working...

Peter wrote:
> 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

Hi Peter.

Seems like this is what you want:

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   and h.INSTRUMENT_CODE = 'AFI'

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   and h.INSTRUMENT_CODE = 'AFI'

HTH

--
David Portas