Pete,
Everything you've said so far indicates that you're looking for LEFT OUTER
JOIN (give me all in A, regardless if it's also in B). For non-matching
rows in B, query will return NULL for any fields projected from B. For
those fields you can use COALESCE(B.field,'yourDefault') so you don't have
to handle NULLs in your program.
Now, joining 5 files... it's never easy. Join 2, then when happy, join the
next one to the original 2 etc. You can use parenthesis you know :)
Perhaps this link will help:
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/sqlp/rbafyjoin.h
tm
Elvis
Mike Cain - DB2 for i5/OS Temporary Indexes - The Good, The Bad, The Ugly
October 16
2007 System i Fall Technical Conference | Orlando | November 4-7
Celebrating 10-Years of SQL Performance Excellence on IBM System i, eServer
iSeries and the server affectionately known as the AS/400
-----Original Message-----
Subject: Re: Making Matched Records with Primary File in Query an Outer
joinin SQL
Should I use coalesce for all the select fields then? I tried using
coalesce(fieldName,' ') on the two fields that appear in the query as
"blank" values but I still have no "missing" records as I run the Query
and the SQL side by side. I am guessing that it is due to my join logic
or the where clause, but I haven't tripped over the "magic" combination yet.
Thanks,
Pete
midrange.com
