Re: Join Files

If primary file is 
....+....1....+....
KEYFIELD  CUSTNAME 
   A      BUBBA 
   C      Charlie 

and secondary file is
....+....1....+....2....+....3..
KEYFIELD  TRANSACTIONID   AMOUNT
   A                  1     5.20
   B                  1     9.20

And you want:
....+....1....+....2....+....3....+....4....+....5....+....6....+....
BOTHKEYFIELD  KEYFIELD  CUSTNAME    KEYFIELD  TRANSACTIONID   AMOUNT 
     A           A      BUBBA          A                  1     5.20 
     B           -      -              B                  1     9.20 
     C           C      Charlie        -                   -        -

Then do this:

With T1 as (
select p.keyfield as bothkeyfield, 
  p.*, s.* 
from primaryfile p left outer join secondaryfile s 
using (keyfield) 
union 
select s.keyfield as bothkeyfield, 
  p.*, s.* 
from primaryfile p right exception join secondaryfile s
using (keyfield) 
) 
Select * from T1 
order by bothkeyfield, coalesce(transactionid,0) 

I think this is called a "full outer join"  at least according to page 363 
of Conte's book.  However when I try "full outer join" in STRSQL it says 
nasty things about my sainted mother.

Archaic versions of i5/os will have to replace the more intuitive "using 
(...)" with "on p.keyfield=s.keyfield".  Of course most i5 databases were 
incorrectly setup in the first place as to have different field names 
because of some silly prefix or suffix based on the file name.

If you want this as a logical file you can try putting the whole statement 
into CREATE VIEW, but I was having an issue with that.  The issue was with 
the "With T1 as".  I question if this is allowed within a view.

Of course, if you don't like the nulls displayed above you can deal with 
them by using coalesce.

Rob Berendt