RE: Matching Records

In your example, which is very concise, I remember
that you need to specify end of file processing on
both the primary and secondary files or the program
will end once all records are read from the primary
file regardless if more records remain in the
secondary file. So the file spec would be IPE for the
primary and ISE for the secondary. Maybe someone else
remembers this subtlety or has additional light to
shine on the topic.


--- Joe Pluta <joepluta@xxxxxxxxxxxxxxxxx> wrote:

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

--
This is the RPG programming on the AS400 / iSeries
(RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the
archives
at http://archive.midrange.com/rpg400-l.