possible to match files with incomplete reference?



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

possible to match files with incomplete reference?

Postby GRAMBO » Thu Dec 02, 2010 11:36 pm

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?
GRAMBO
 
Posts: 8
Joined: Fri Oct 15, 2010 10:22 pm
Has thanked: 0 time
Been thanked: 0 time

Re: possible to match files with incomplete reference?

Postby skolusu » Fri Dec 03, 2010 5:03 am

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   
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post