Need help on Join of two files!!!



Support for NetApp SyncSort for z/OS, Visual SyncSort, SYNCINIT, SYNCLIST and SYNCTOOL

Need help on Join of two files!!!

Postby 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:-
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!!!!!
ibmmf4u
 
Posts: 65
Joined: Wed Dec 14, 2011 10:26 pm
Has thanked: 0 time
Been thanked: 1 time

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

Postby 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.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

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

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

//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 !!!
ibmmf4u
 
Posts: 65
Joined: Wed Dec 14, 2011 10:26 pm
Has thanked: 0 time
Been thanked: 1 time

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

Postby BillyBoyo » Mon May 14, 2012 11:57 pm

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)
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

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

Postby ibmmf4u » Tue May 15, 2012 12:11 am

Thanks a lot Bill. It worked fine.

Thanks a lot for your help once again!!!!
ibmmf4u
 
Posts: 65
Joined: Wed Dec 14, 2011 10:26 pm
Has thanked: 0 time
Been thanked: 1 time


Return to Syncsort/Synctool

 


  • Related topics
    Replies
    Views
    Last post