Joined: 03 Feb 2007 Posts: 175 Location: PUNE(INDIA)
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.
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:
and FileB has:
and you show the output as:
111 ABCD XYZ
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.
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.)