But the problem is file2 may/may not have all the key columns matching with an input records and it can have multiple records matching for an input record like for example
Code:
key1 key2 key3 key4 key5 col6
M M M M M Val1
M M M M - Val2
M M M - - Val3
M M - - - Val4
M - - - - Val5 M means Match
- wild carded
The above depicted situation is the order in which the search needs to be done. In such a case the best possible match, i.e the first record should be chosen. Please note that there can be other combinations also in file2 but the only combinations that affect the output are the ones mentioned above and in order of their priority.
The output file should only have matching records and exactly one record(the best match) for an input record.
Could you please advice if this can be done through pure JCL../DFSORT etc
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
I'm not convinced yet that this can't be done with DFSORT/ICETOOL, but I need more information to know for sure.
What is the RECFM and LRECL of the each input file?
Please show an example of the records in each input file (relevant values) and what you expect for output. Please show as many variations as you can. You can use something simple for each key value and col value such as a letter or number to keep it simple.
Thanks for your response. Actually FILE1 and FILE2 are table unloads and can be made any RECFM and although the record size for both the input tables vary, the files can still be made to have the same lrecls by adding a filler in one or both the input files.
Lets assume all the columns in both the files are char(5).
Now lets assume the following data in FILE1
KKKK1KKKK2KKKK3KKKK4KKKK5CCCC1CCCC2CCCC3CCCC4CCCC5
KKKK6KKKK7KKKK8KKKK8KKK10CCCC6CCCC7CCCC8CCCC9CCC10
Corresponding to the first record of FILE1, 6th record of file2 is the best match, i.e NNNN6. For record 2 of FILE1 there is no match hence it will not go in the o/p file.
Lets assume file2 has the first two records only..i.e.
In this case both the records occupy same position priority wise but since first record is found first, the effective value for the output will be NNNN1
Again if we assume the FILE2 just has the following three records.
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
I'm still not sure I understand your priority scheme. I think that a match of key1 and key2 and key3 has a higher priority than a match of key1 and key2. But does a match of key1 and key3 and key5 have a higher or lower priority than a match of key1 and key2?