Re: Matching Records

Absolutely.

Sometimes the tried and true works best.

ALL HAIL THE RPG LOGIC CYCLE! ! ! ! !

Mark Walter
Paragon Consulting Services, Inc.
Certified IBM RPG Developer
717-764-7909 Ext. 26
mwalter@xxxxxxxxxxxxxxx
www.paragon-csi.com



Booth Martin <booth@xxxxxxxxxxxx>
Sent by: rpg400-l-bounces+mwalter=paragon-csi.com@xxxxxxxxxxxx
11/06/2007 09:51 PM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
cc

Subject
Re: Matching Records






In other words, matching records processing with primary and secondary
files is the right choice for this project?

Joe Pluta wrote:

From: Brian Johnson

AKA full outer join. Not yet directly supported on System i, but IBM
provides an example for simulating one...

http://publib.boulder.ibm.com/iseries/v5r2/ic2924/index.htm?info/sqlp/rbaf

ymst124.htm

Thanks Brian! This made me do some more research, and I found that in
addition to the FULL OUTER JOIN (which as you indicate is exactly what

we're

looking for), there are also a set of RIGHT JOIN functions, which

basically

reverse the selection process.

A RIGHT EXCEPTION JOIN returns records that only exist in the right

file,

while RIGHT OUTER JOIN includes those records along with records that

exist

in both. This exactly mirrors the LEFT EXCEPTION JOIN AND LEFT OUTER

JOIN.

For some databases, LEFT JOIN is a synonym for LEFT OUTER JOIN, while

RIGHT

JOIN is synonymous with RIGHT OUTER JOIN. Note that there is no LEFT

INNER

JOIN (or RIGHT INNER JOIN) because an INNER JOIN includes only records

that

are contained in both files, so the RIGHT vs. LEFT distinction makes no
difference in record selection.

As Brian pointed out, FULL OUTER JOIN returns all records, whether they
exist in both files or not. Interestingly, there is no FULL EXCEPTION

JOIN,

which would return records that only exist in one file or the other, but

not

both (but it's easy to simulate with a UNION of LEFT EXCEPTION and RIGHT
EXCEPTION).

Finally, various databases support some or all of these requests. Oracle
supports pretty much everything, although I don't think it likes the

short

synonyms. You need to consult the documentation for your database to
determine which functions are supported.

Joe

P.S. I won't tell you about CROSS JOIN. You don't want to know.