Re: Need Help

[Vern Hamberg <hambergv@xxxxxxxxxxxxxx>]

> 
> At 09:26 AM 7/31/97 -0500, you wrote:
> >Hello,
> >
> >I am looking for some advise.  I am working on a project, this project
> >requires that I compare records from 2 files.  File A and File B.  File
> >A is the primary file and File B is the secondary file.  I need to
> >compare the records that are in File A to the records in File B.  What I
> >am having a problem with is if there is a record in File B that is not
> >in File A I need to know that.  I Was going to read File A and chain out
> >to File B to see if the record exists in File B.  I am having problems
> >on how to determine if there is a record in File B that is not in File
> >A.  Any help would be greatly appreciated.
> >
> Hi,
> 
> I assume that there is a common key field.  With this assumption, there are
> two ways to go.  You can set up a multi-record format logical file, or use
> matching record logic in RPG.
> 

Other options involve OPNQRYF or SQL. You could run OPNQRYF twice, the
first time with File A as primary and JDFTVAL(*NO)--same as an SQL INNER
JOIN. Then CPYFRMQRYF MBROPT(*REPLACE) to make a workfile. The second one
would use File B as primary and JDFTVAL(*YES)--this is an SQL LEFT OUTER
JOIN. Then, CPYFRMQRYF MBROPT(*ADD). This gives you a file that has
everything you want.

With SQL (either with the IBM licensed product or something like Midrange
Computing's EXCSQLSTM), the above can be done in a single statement,
something like 

        select filea.*,fileb.* from filea join fileb on fielda=fieldb
        union
        select filea.*,fileb.* from fileb outer join filea on fielda=fieldb

BTW, this is for v3r7. V3r2 does not have the OUTER JOIN--you have to fake
it with a UNION of a JOIN and a WHERE NOT EXISTS kind of thing.

There's usually a way to have this output to an outfile.

Cheers


Vernon Hamberg
System Software Programmer
Old Republic National Title Insurance Company
400 Second Avenue South
Minneapolis, MN 55401
(612) 371-1111 x480
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* This is the Midrange System Mailing List!  To submit a new message,   *
* send your mail to "MIDRANGE-L@midrange.com".  To unsubscribe from     *
* this list send email to MAJORDOMO@midrange.com and specify            *
* 'unsubscribe MIDRANGE-L' in the body of your message.  Questions      *
* should be directed to the list owner / operator: david@midrange.com   *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *