Re: ANSI joins not working...

David Portas wrote:
> 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

I'm wondering how yhour code:

 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'

differs from the OPs original ANSI code:

 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'

 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'

as I don't see a difference. This is the same code the OP is reporting as 'not working', by which he means it doesn't return the same results as his original query.

Looking at the 'working' query again:

 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'

the OP hasn't correctly translated his original into ANSI, and, neither have you. Note the outer 'join' on the literal value; this is not included in the OP's original ANSI syntax nor in your identical offering. Possibly this is what the OP needs:

 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 p.INSTRUMENT_CODE = h.INSTRUMENT_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 p.INSTRUMENT_CODE = h.INSTRUMENT_CODE

   and h.INSTRUMENT_CODE (+)= 'AFI'

Notice the additional outer join criteria including the INSTRUMENT_CODE column; the literal assignment should create the same condition as the original, non-ANSI code.

David Fitzjarrell Received on Tue Jun 14 2005 - 08:02:31 CDT