Re: ANSI joins not working...

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

I would be very careful with full outer join and putting the query predicate to the ON clause - you are joining actually on modified condition. The result might not be that you expect.Directly you can not compare the old (+) syntax with the full outer join - before ANSI syntax Oracle hadn't outer join. So , consider this:

SQL> set pages 999
SQL> set lines 200
SQL> col dname for a15
SQL> col ename for a15
SQL> SELECT d.deptno,d.dname,e.ename,e.sal

   2 FROM dept d FULL OUTER JOIN emp e
   3 ON d.deptno=e.deptno AND e.sal < 2000    4 /

     DEPTNO DNAME           ENAME                  SAL
---------- --------------- --------------- ----------
         20 RESEARCH        SMITH                  800
         30 SALES           ALLEN                 1600
         30 SALES           WARD                  1250
         30 SALES           MARTIN                1250
         30 SALES           TURNER                1500
         20 RESEARCH        ADAMS                 1100
         30 SALES           JAMES                  950
         10 ACCOUNTING      MILLER                1300
         40 OPERATIONS
                            JONES                 2975
                            BLAKE                 2850
                            CLARK                 2450
                            SCOTT                 3000
                            KING                  5000
                            FORD                  3000

15 Zeilen ausgewählt.

SQL> Best regards

Maxim Received on Tue Jun 14 2005 - 14:37:24 CDT