IBM Mainframe Forum

by **ibmmf4u** » Mon May 14, 2012 9:02 pm

Hi Everyone,

My requirement goes in this way. I need to join two files. Below mentioned were the two input files:-

File1:-

Select all

TEST NUM
====== =====
TEST1 0200
TEST3 0300
TEST4 0400
TEST2 0500
TEST5 0600

File2:-

Select all

TEST NUM
====== =====
TEST1 0300
TEST2 0200
TEST3 0400
TEST4 0600
TEST5 0400

I need to join two files. Where in the fields in column TEST can act as a key field and it compares the Values present in the NUM column of both the files and value's in the NUM column of File1 should be less than the value in File2 . The output file should contain the records of file1 based on the above conditions.

The fields that meet the above criteria should be of in one file and rest should be in another file.

Outputfile1:-

Select all

TEST NUM
====== =====
TEST1 0200
TEST3 0300
TEST4 0400

Outputfile2:-

Select all

TEST NUM
====== =====
TEST2 0500
TEST5 0600

Can someone provide me the sortcard in achieving the above if possible.

Thanks a lot in advance!!!!!

by **BillyBoyo** » Mon May 14, 2012 9:36 pm

So, you do the join and then you have an OUTFIL INCLUDE=(start1,length1,type1,LT,start2,length2,type2) from the REFORMAT record.

by **ibmmf4u** » Mon May 14, 2012 11:41 pm

Hi Bill,

Thanks a lot. It helped me in achieving it to a major extent . However i have few questions over here.Please kindly help me with your answers.

Below mentioned is the code which was working :-

Select all

//xxxxxxxx JOB (xxxxx,xxxxx),'THANKS BILL',CLASS=R,MSGCLASS=U,
// NOTIFY=&SYSUID
//STEP010 EXEC PGM=IEFBR14
//FILE1 DD DSN=TEST.MATCH.FILE,
// DISP=(NEW,CATLG,DELETE),
// SPACE=(CYL,(96,50),RLSE),
// DCB=(MODLDSCB,RECFM=FB,LRECL=19)
//FILE2 DD DSN=TEST.UNMATCH.FILE,
// DISP=(NEW,CATLG,DELETE),
// SPACE=(CYL,(96,50),RLSE),
// DCB=(MODLDSCB,RECFM=FB,LRECL=19)
//STEP020 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTJNF1 DD *
TEST1 0200
TEST3 0300
TEST4 0400
TEST2 0500
TEST5 0600
//SORTJNF2 DD *
TEST1 0300
TEST2 0200
TEST3 0400
TEST4 0600
TEST5 0400
//MATCH DD DSN=TEST.MATCH.FILE,DISP=SHR
//SORTOUT DD SYSOUT=*
//UNMATCH DD DSN=TEST.UNMATCH.FILE,DISP=SHR
//SYSIN DD *
JOINKEYS FILES=F1,FIELDS=(1,5,A)
JOINKEYS FILES=F2,FIELDS=(1,5,A)
REFORMAT FIELDS=(F1:1,15,F2:9,4)
SORT FIELDS=COPY
OUTFIL FNAMES=MATCH,INCLUDE=(10,4,CH,LT,16,4,CH)
OUTFIL FNAMES=UNMATCH,SAVE
/*

Output files:-

Match file:-

Select all

TEST1 0200 0300
TEST3 0300 0400
TEST4 0400 0600

UNMatch file:-

Select all

TEST2 0500 0200
TEST5 0600 0400

But both the Match and Unmatch file conatins the feilds three columns , where the col-1,col-2 are from file-1 and col-3 is from file-2.

I don't want this column to present in the Match file, can you please let me know is there a way in avoiding this (by reformating/overlaying the record).

Kindly help me out !!!

by **BillyBoyo** » Mon May 14, 2012 11:57 pm

This will shorten the record on MATCH to only the first 15 bytes, for example.

Select all

OUTFIL FNAMES=MATCH,INCLUDE=(10,4,CH,LT,16,4,CH),
BUILD=(1,15)

by **ibmmf4u** » Tue May 15, 2012 12:11 am

Thanks a lot Bill. It worked fine.

Thanks a lot for your help once again!!!!

IBM Mainframe Forum

Need help on Join of two files!!!

Need help on Join of two files!!!

Re: Need help on Join of two files!!!

Re: Need help on Join of two files!!!

Re: Need help on Join of two files!!!

Re: Need help on Join of two files!!!