Page 1 of 1

possible to match files with incomplete reference?

PostPosted: Thu Dec 02, 2010 11:36 pm
by GRAMBO
I have the following files, is there anything I can do to match using icetool? Ideally I would like a file of those items that match, and those that don't, so I can then do some other processing on these items. From file 2, I'm trying to obtain the 'P' number in position 37,12 for each item in file 1 that I can find in file 2.

File 1 - (18 digit account, plus 12 digit reference)
001500753000086796SL44764520
001500753000086796W1484025
0015007530000867960180519101
001500753000086796ABWORLD 1234

File 2 - (18 digit account, plus 18 digit reference)
001500753000086796SL44764520 P00000016101
001500753000086796ABWORLD 1234562518P00000016102
001500753000086796XXZZ005363041080 P00000016103
0015007530000867960180519101 P00000016104
001500753000086796XX005363040980 P00000016105

File 1 has around 100,000 items which I need to match with around 1 million items in file 2. The problem is in file 1, only 12 characters of the 18 reference are held (pos 13-18 are spaces), whereas in file 2, the full 18 characters are held.
eg.
File 1 -001500753000086796ABWORLD 1234
File 2- 001500753000086796ABWORLD 1234562518P00000016102

Many of the items in file 1 will have a reference of 12 characters or less, so it will be possible to get a match. It is also possible that there may be more than 1 match.

Is there any way of generating some sort of match for the items where the reference is 12 characters or less, and putting the unmatched items in a seperate file for further processing?

Re: possible to match files with incomplete reference?

PostPosted: Fri Dec 03, 2010 5:03 am
by skolusu
GRAMBO,

It is NOT clear as to what your output is.

I assumed that your both input files are FB recfm and LRECL of 80. You need to match the 18 digit account first and then the 12 byte reference. For file 2 you only need to pick the records which has 'P' in pos 37

in your example there is only 1 record that has p in pos 37 that is 001500753000086796 key and it has 4 records in file 1. However only 1 record has the full 12 bytes ABWORLD 1234 which matches file 2. so this record from file will be written out to REFMATCH file and the other 3 records will be written out to NOMATCH

//STEP0100 EXEC PGM=SORT                                   
//SYSOUT   DD SYSOUT=*                                     
//INA      DD *                                             
001500753000086796SL44764520                               
001500753000086796W1484025                                 
0015007530000867960180519101                               
001500753000086796ABWORLD 1234                             
//INB      DD *                                             
001500753000086796SL44764520 P00000016101                   
----+----1----+----2----+----3----+----4----+----5----+----6
001500753000086796ABWORLD 1234562518P00000016102           
001500753000086796XXZZ005363041080 P00000016103             
0015007530000867960180519101 P00000016104                   
001500753000086796XX005363040980 P00000016105               
//REFMATCH DD SYSOUT=*                                     
//NOMATCH  DD SYSOUT=*                                     
//SYSIN    DD *                                             
  OPTION COPY                                               
  JOINKEYS F1=INA,FIELDS=(1,18,A)                           
  JOINKEYS F2=INB,FIELDS=(1,18,A)                           
  JOIN UNPAIRED                                             
  REFORMAT FIELDS=(F1:1,80,F2:1,80,?)                       
                                                           
  OUTFIL FNAMES=REFMATCH,BUILD=(1,80),                     
  INCLUDE=(19,12,CH,EQ,99,12,CH,AND,161,1,CH,EQ,C'B')       
  OUTFIL FNAMES=NOMATCH,BUILD=(1,80),                       
  INCLUDE=(19,12,CH,NE,99,12,CH,AND,161,1,CH,EQ,C'B')       
//JNF2CNTL DD *                                             
  INCLUDE COND=(37,1,CH,EQ,C'P')                           
//*


The REFMATCH file contents
001500753000086796ABWORLD 1234   


The NOMATCH file contents
001500753000086796SL44764520   
001500753000086796W1484025     
0015007530000867960180519101