Re: re: Outer Joins
I dont really see what all the fuss is about...
If you use outer joins follow a very simple rule:
Dont apply ANY filter conditions to the outer joined table.
If you want to see this in action try something like our old friend the
How do i get a list of rows from one table that dont exist in another?:
select * from taba,outer tabb
where taba.col1=tabb.col1
and tabb.col1 is null { pick up all rows that dont join)
well this DONT WORK....
If theres no matching row, null is picked up - including testing for
nulls
If you want to apply any filter conditions here my tuppence worth :
SQL:
Use a temporary table
Eg.
select taba.*,tabb.col1 testthis from taba,outer tabb
where taba.col1=tabb.col1
into temp temp_1;
select * from temp_1 where testthis is null
4GL:
place the filter condition in the foreach loop
Eg.
foreach curs_1 into w_taba.*,w_tabb.*
if w_tabb.col1 is null then
contiune foreach
end if
--
Mike Aubury
Senior Technical Consultant