Re: ANSI joins not working...

> 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'

Not so. In his ANSI queries Peter referenced the unpreserved tables in the WHERE clause. So his queries are logically equivalent to an INNER join. My version moved that predicate to the ON clause, which makes a big diffrence to the meaning of the query.

--
David Portas