SPLICE two files - With key at same position.

craig2020 · New User Joined: 30 Jan 2007 Posts: 7 Location: Chennai

Hi,

I have the below requirement to merge two files laterally.

File details
File A - FB, 80, No of fields - 7, Key field is at position 1 to 8, the file sorted on key field.
File B - FB, 80, No of fields - 3, Key field is at position 1 to 8, the file sorted on key field.

File A - data (Key is not unique)

AAA12345 Record1
AAA12345 Record2
AAA12456 Record3
BBC99999 Record4
BBC99999 Record5
CDE34561 Record6

File B - data (Key is unique)

AAA12456 Phone1
BBC99999 Phone3

Expected Output file ( The output should contains all the records in file A, If file B Key column has any match with File A key column, then the corresponding details has to be appended at the end of File A)

AAA12345 Record1
AAA12345 Record2
AAA12456 Record3 Phone1
BBC99999 Record4 Phone3
BBC99999 Record5 Phone3
CDE34561 Record6

I have tried the below SPLICE technique, but not getting the expected output. Please help. Thanks.

COPY FROM(SORTIN1) TO(TEMP1) USING(CTL1)
COPY FROM(SORTIN2) TO(TEMP1) USING(CTL2)
SPLICE FROM(TEMP1) TO(SORTOUT) ON(1,8,CH) WITHALL KEEPNODUPS -
WITH(81,72)

CNTL1 - OUTREC BUILD=(1:1,80,81:80X)
CNTL2 - OUTREC FIELDS=(1:1,8,9:72X,81:9,72,153:8X)
CNTL3 - OUTFIL FNAMES=SORTOUT,OUTREC=(1:1,160)

Here SORTIN1 is File A, SORTIN2 is File B

expat · Posted: Wed Aug 12, 2009 12:39 pm

What are the unexpected results.

One common error I make is by reading in the two datasets in the wrong order. Try this, reading in the file without duplicates first.

craig2020 · New User Joined: 30 Jan 2007 Posts: 7 Location: Chennai

Hi Expat,
Thanks for your response..

I missed to mention one more thing in File B.

File B - data (Key is unique)

AAA12456 Phone1
BBC99999 Phone3
XXX99999 Phone3

Still the expected output in post 1 holds true.

The result I am getting is.

AAA12345 Record1
AAA12345 Record2
AAA12456 Record3 Phone1
BBC99999 Record4 Phone3
BBC99999 Record5 Phone3
CDE34561 Record6
XXX99999 <space> Phone3

I could not able to remove the record number 7, which is marked in Bold.

Skolusu · Posted: Wed Aug 12, 2009 9:16 pm

Craig,

Since both input files are of the same lrecl and recfm , we really don't have to use splice to get the desired results. We can use the new function when=group to push the contents of file2 on to file 1 record

Use the following DFSORT JCL. We concatenate 1 line record with HDR before each file so as to identify as to which file the record came from.