Re: ANSI joins not working...

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

>

>

I suppose had I removed the (+) from the second example the code would be valid. We all make typographical errors.

  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'

Possibly that bird will fly.

David Fitzjarrell Received on Tue Jun 14 2005 - 10:05:01 CDT