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
That's a good catch about the additional join. I expect your LEFT OUTER
version may be what the OP intended. But your second example is not
valid in the ANSI standard.
--
David Portas
Received on Tue Jun 14 2005 - 08:27:28 CDT
