Joined: 03 Feb 2007 Posts: 175 Location: PUNE(INDIA)
maruniral,
The number of your sample records are too less to understand your requirement. Please give more sample records. Along with that, if you could write what exactly you want, you requirement will be understood better.
Basically its a Many to Many relationship where i want to merge the 2 files based on a particular key. Also the o/p should contain all records from FILE A and any record match which is found from FILE B. The below example will help you understand.
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
maruniral,
You still haven't made it clear what you're trying to do. You say it's a many-to-many relationship but you haven't explained that relationship. For example, FileA has:
111 ABCD
111 EFGH
and FileB has:
111 XYZ
and you show the output as:
111 ABCD XYZ
111 EFGH
So you're doing a join for the 111 ABCD record with the 111 XYZ record, but not doing a join for the 111 EFGH record. You need to explain the rules you're using to get that output from that input. Likewise, you need to explain the rules for the 222 keys.
Until you explain the rules completely, nobody can tell you whether or not (let alone how) it can be done.
Rule is that i need all the records from FILEA. Also if there is a key match between FILEA and FILEB then i need all matched records from FILEB also but they should be merged with subsequent records from FILEA. So in case there are only 2 records from FILEA and there are 3 records from FILEB whose keys are same then the first 2 records from FILEB should be merged with the 2 records from FILEA and the 3rd record should come as it is.
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
I think this DFSORT/ICETOOL job will do what you want. I assumed your input files have RECFM=FB and LRECL=80, but the job can be changed appropriately for other attributes.
Thanks a Lot Frank.
One more issue that i have is that FILEB can have multiple records with the same key but would not have any match with any Key in FILEA in which case i dont want those in the o/p.
Considering the same example
If FILEB has 2 records as 555 in the key field then i think that would come in the o/p.
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
You didn't mention that requirement previously so I didn't set up the job to handle it. Now I'm wondering what else you haven't mentioned or if I really understand what you want. So to make things clearer, please tell me what your expected output records would be for these input records. (If I missed any variations, feel free to add them.)