RE: Problem with outer join and retrieving null column values

[Zabach, Elke]

Pat and Helen Harms  wrote:  I'm having a problem using left outer joins and retrieving the column values which are null.   The following SQL demonstrates the problem:   Create Table Table1    (Col1 Decimal(38) Not Null,     Col2 Decimal(38) Null,     Primary Key (Col1)    ) // Create Table Table2    (Col1 Decimal(38) Not Null,     Col2 Decimal(38) Null,     Primary Key (Col1)    ) // Insert Into Table1 Values( 1 , 1) // Insert Into Table1 Values( 2 , 2) // Insert Into Table2 Values( 1 , 1) // commit // select t1.col1, t1.col2, t2.col1, t2.col2 from table1 t1, table2 t2 where ( t1.col1 = t2.col1 (+) ) // select t1.col1, t1.col2, t2.col1, t2.col2 from table1 t1, table2 t2 where ( t1.col1 = t2.col1 (+) )   and ( t2.col1 is null) //   The first query returns two rows: 1,1,1,1 2,2,?,?   The second query returns: 1,1,?,? 2,2,?,?   I was expecting just one row: 2,2,?,?   Which is how Oracle behaves.   Can anyone explain to me what is happening here ? Can I get it to behave how I want ?  Hi,   this is exactly the misbehaviour we are just working on. For some internal reason the ( t2.col1 is null) is done BEFORE the outer join is performed, not AFTER as expected.   I think it will be corrected with version 7.3.0.21.   Elke SAP Labs Berlin