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



This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].