compare 2 file based on key+retain duplicates ICETOOL SPLICE

Hariharan BK · New User Joined: 11 Sep 2011 Posts: 14 Location: India

Hi!

I am working on an requirement where there are 2 files :
FILE 1 - have all accounts with other details and will appear with duplicates.
FILE 2 - have only the account list.

All records in file 1 should be written to o/p file (with dups), provided those accounts are matching with that of file 2.

File 1:

100 XXXXX
100 XXXXY
121 XXXRR
122 RREED

File 2:

100
121

o/p file expected:

100 XXXXX
100 XXXXY
121 XXXRR

Please advise on how to get this requirement done via DFSORT/ICETOOL (SPLICE).

I have tried using SPLICE options like 'WITHALL', 'WITHANY', 'WITHEACH' etc. but the duplicates are getting removed and only 1 record is appearing in output file when there are many in file 1... Please suggest.

Thanks,
Hari

dick scherrer · Posted: Thu May 02, 2013 12:38 am Post subject:

Hello and welcome to the forum,

You should post the code you are using and the diagnostic output from a run (including the messsage ids).

I suspect you do not need SPLICE, but a JOINKEYS.

There are also topics that do this available in the forum. Use the SEARCH feature (above).

mistah kurtz · Posted: Thu May 02, 2013 10:34 am Post subject:

Hi Hari,

As d.sch. has already pointed out, you dont need a SPLICE but JOINKEYS.
Assuming that First three character is the key in both the files and you need all the data from file one only, and all the files are FB and LRECL=80, you can try this job:

Bill Woodger

The sample data shown is already sorted, so no need to let JOINKEYS do it again. By not specifying a JOIN statement, only "matched" records will be included, and the REFORMAT record can specify all the data needed from file F1.

Hariharan BK · New User Joined: 11 Sep 2011 Posts: 14 Location: India

Many thanks All (mistah kurtz)... It worked fine..

Apologies for not posting the code earlier, as there were environement issues in getting the code into this forum.

Anyways thanks again !!

mistah kurtz · Posted: Thu May 02, 2013 7:03 pm Post subject:

Hi Hari..You're welcome..I think Bill's solution is simpler and more efficient as per your requirements.

Bill Woodger

Hey, it is your solution, I've just "tweaked" it for the data shown.

JOINKEYS sorts each data file by default (with OPTION EQUALS).

If either or both of the files are already sorted, then you want to tell JOINKEYS not to Sort, and you can avoid "sequence checking" as well.

If only a match is needed, no need to deal with unmatched for either file. If there is no match marker to drop off, then the REFORMAT record can specify the full and complete output.

I'm sure there are many thousands of JOINKEYS "out there" which work, but which re-sort sorted files :-)