Page 1 of 1

Need help on Join of two files!!!

PostPosted: Mon May 14, 2012 9:02 pm
by ibmmf4u
Hi Everyone,

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

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


File2:-
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:-
TEST    NUM
======  =====
TEST1   0200
TEST3   0300
TEST4   0400


Outputfile2:-
TEST    NUM
======  =====
TEST2   0500
TEST5   0600


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

Thanks a lot in advance!!!!!

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

PostPosted: Mon May 14, 2012 9:36 pm
by BillyBoyo
So, you do the join and then you have an OUTFIL INCLUDE=(start1,length1,type1,LT,start2,length2,type2) from the REFORMAT record.

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

PostPosted: Mon May 14, 2012 11:41 pm
by ibmmf4u
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 :-

//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:-

TEST1    0200  0300
TEST3    0300  0400
TEST4    0400  0600


UNMatch file:-
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 !!!

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

PostPosted: Mon May 14, 2012 11:57 pm
by BillyBoyo
This will shorten the record on MATCH to only the first 15 bytes, for example.

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

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

PostPosted: Tue May 15, 2012 12:11 am
by ibmmf4u
Thanks a lot Bill. It worked fine.

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