Joined: 13 Nov 2008 Posts: 11 Location: ALICANTE - Spain
Hi to everyone and thank you for your info. please sorry for my english icon_smile.gif
I have two input files with a key field in both. I need to make a match to get an output file with only the necessary fields: is the same structure of the second.
The file 1 contains a list of customers and his accounts, and the file 2 all data os those accounts.
The result i need is in the output file should be all the records in the second file that exist in file 1.
Note:In this example, records are unsorted, but in my jcl they are sorted by key.
I've been trying the sample of Splice Operator ('11 ','22' and'12 ', ...) from publibz.boulder.ibm.com but it is still not make it work.
I do not know how to use the WITH, because they have differents size and in different order ...
Someone could help me please ... thank you very much
There are lot many previous posts on this topic. You may try searching those.
I was going thru your requirements and had a doubt regarding the keys of input files.
You have written :
Quote:
Being TEXTXX text or numeric fields...
Key an ON(9,18) on files 1 and key ON(1,18) on file 2.
Well , how can the length of keys be different in both files. If you want to match records from both the files. The position of key fields may differ but the length has to be same.
Joined: 13 Nov 2008 Posts: 11 Location: ALICANTE - Spain
Thanks for yoy comments.
Key on file begins on pos 1, and have 18 positions.
And key on file 2 begins on position 1 and has 18 positions also.
I 'have try, and search examples of topic, but all post i have found, has the key's file on initial position on both, or file1 and file2 has the same size.
I'va use the serch before post this topic, sorry if this requeriment was answered, but i didn't found (and sorry for my english, please correct me, i want to learn).
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
If neither file has duplicate records within it, as shown in your example, you don't need SPLICE for this ... you can do it more simply with a DFSORT/ICETOOL SELECT job like the following:
For more information on different techniques for matching, see the "Create files with matching and non-matching records" Smart DFSORT Trick in the my revised paper at:
Out (solution1). must be tha same estructure than original, but inserting 8 first positions of File1 in pos 38 (i think) of File2.
File1 and File2 has duplicates, and file
I've been trying move to the end of file data i need, use all the examples of 'VV', 'BB', '11', '22' and tricks of your "Smart DFSORT tricks" (more nearby is "Key in different places, duplicates" but i don't know how to make it work.
Can you help me? thank you for all your advices.
I expect not to have committed mistakes to post the requeriment now.
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
Do both files have duplicates within them as you've shown? For example, file1 has three 999911112233333333 keys and file2 has two 999911112233333333 keys. If so, the matching you want isn't clear. For those dup keys, you show two output EUR records, one with Xs and one with Ys, but since there are three file1 records with that key, shouldn't there be three EUR records instead of two EUR records in the output? Please explain the "rules" you're using to go from input to output.
"since there are three file1 records with that key, shouldn't there be three EUR records instead of two EUR records in the output?"
Is correct, but with the relationship of same account-customer-currency, this record isn't needed, This is why I have omitted.
But if isn't possible, i'll omitt it in a program or in a next step.
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
So you're saying that if multiple file1 records have the same key (e.g. 999911112233333333) and the same value in positions 1-8 (XXXXXXXX), you can process the file1 records as if there was only one record with that key and value (e.g. process the first record with 999911112233333333 and XXXXXXXX and ignore the second) - right?
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
This is a cartesian join. Kolusu might be able to show you how to do this with SPLICE, but he's on vacation this week.
I could show you how to do it with an Assembler program. If you want me to do that, send me a note offline (yaeger@us.ibm.com) referring to this thread.
This can be done with splice. Although I have code to execute cartesian join , given by Kolusu.
Yes, I know. I said that in my previous post. But Kolusu is on vacation this week and I don't have the time to adapt his trick for this instance. That's why I offered to supply an Assembler Program ( already have one I can adapt and it's also more efficient than the SPLICE trick).