RE: Matching Records

From: Adam Glauser

Booth Martin wrote:

lol... I looked at the request and said... " A simple 20 line program,
max, and somehow it'll be turned into a 500 line beast that no one can
understand in under a 1/2 hour."

I don't know ... I think the SQL version would be relatively
straightforward. Maybe something like

SELECT file1.field1, file1.field2....,file1.fieldN, file2.field1,
file2.field2....,file2.fieldN

FROM file1 LEFT EXCEPTION JOIN file2
ON <matching fields>


UNION


SELECT file1.field1, file1.field2....,file1.fieldN, file2.field1,
file2.field2....,file2.fieldN

FROM file1 RIGHT EXCEPTION JOIN file2
ON <matching fields>

For me, that's way easier than learning how to write a matching records
program, but that's because I've known SQL longer than RPG.

Oops, Adam... you forgot the records where they exist in both files but
don't match. With matching records you would probably want to clone an
existing program; this would tell you how to define the F-specs and the
I-specs (it's really quite simple, and I hope the code makes it through).

Ffile1 ip e k disk
Ffile2 is e k disk
FReport o e printer

Ifile1rec 01
I file1key1 m1
I file1key2 m2
I file1key3 m3

Ifile2rec 02
I file2key1 m1
I file2key2 m2
I file2key3 m3

/free
if not *inmr or
((file1data1 <> file2data1) or
(file1data2 <> file2data2) or
(file1data3 <> file2data3) or
(file1data4 <> file2data4));
write printrec;
endif;
/end-free

Sure, it's alien to someone who doesn't understand RPG, but it's not that
hard to figure out. You have two files, primary and secondary (not unlike
two files in a JOIN, left and right). You define the two files in the
F-specs. In the I-specs you give each record an identifier (01 or 02, like
an alias in an SQL statement), and you identify the match fields.

In your code, you check for two things: *INMR and the data fields. *INMR is
on if there is a matching record in both files, so if it is off, you have a
mismatched record. The record ID (01 or 02) tells you which one. If *INMR
is on, you now have to check the data fields to be sure they match.

In my case, I have three key fields and four data fields. The MR tells me
whether I have records whose keys don't match, and the compare tells me if I
have records whose data doesn't match.

While I sympathize with Jerry's point, I think everyone should try every
basic tool once where appropriate. Matching records in no harder than a
UNION of JOINs in my book.

Joe