Can anyone please help me out to merge two datasets horizontally using DFSORT as shown below.
For Example,
Data - 1
ID Name
001 Ashok
002 Bharath
002 Sami
003 Anand
Data - 2
DOB ID
07MAR1984 001
02JAN1982 002
04APR1981 002
17NOV1985 003
The output is,
Data - 3
ID Name DOB
001 Ashok 07MAR1984
002 Bharath 02JAN1982
002 Sami 04APR1981
003 Anand 17NOV1985
Is there a DFSORT to achieve this output.
Note:-
Please make note, I want this to be done in DFSORT not in ICETOOL.
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
ashok4u_it,
1. What is the LRECL and RECFM of the input files?
2. What is the position and format of the key to be matched?
3. what are the positions and lengths of other fields to be merged?
Quote:
Is there a DFSORT to achieve this output.
Note:-
Please make note, I want this to be done in DFSORT not in ICETOOL.
ICETOOL is a part of DFSORT since 1991. I just don't understand the restriction. who is making these stupid rules?
Consider this as a VB dataset having same LRECL as 20.
The key field to be matched is ID.
As per our production environment we are not supposed to use ICETOOLS commands as a part of DFSORT. I am not sure about the reason behind this mystery.
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
Hi Ashok,
Quote:
Hi Dick,
Sorry, there it was mistakenly posted. Please forgive for my mistake done accidently. I am working on this scenario for the past 3 days and it is very critical now.
Not to worry
I removed several of our exchanges to get the topic back to your question
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
Quote:
Can you please help me out to resolve this problem.
We'd like to, but you haven't actually described the "rules" for what you want to do clearly, and your restriction against using ICETOOL is problematical.
As for the rules, you show the two files both with these "keys":
001
002
002
003
The "keys" match up in this case. But I suspect there are other possible variations such as:
File1
001
002
002
002
003
File2
001
001
002
002
003
Here the "keys" don't match up and I don't know what rules you want to use for that situation or other possible situations, or even what the other possible situations can be. Your example doesn't tell me much.
We can't give you a solution when we don't understand what you want to do and Kolusu and I really don't have time for guessing games as we have "day jobs" developing DFSORT.
Quote:
I am working on this scenario for the past 3 days and it is very critical now.
Wouldn't that have been enough time to write a program in the language of your choice (or are all languages disallowed at your site as well?).
Sorry Frank. The requirement is like this. File-1 has duplicates records and File-2 is unique. We should merge both the files based upon key fields.
I.e. For each occurance of File-1 should merge with file-2 based upon unique key field.
Example.
Data - 1
DOB ID
07MAR1984 001
02JAN1982 002
04APR1981 002
17NOV1985 003
20SEP1987 004
Data - 2
ID Name
001 Ashok
002 Bharath
003 Anand
The output is,
Data - 3
ID Name DOB
001 Ashok 07MAR1984
002 Bharath 02JAN1982
002 Bharath 04APR1981
003 Anand 17NOV1985
I think now it is pretty much clear. Thanks for your response.
Please do the needful.
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
Here's a DFSORT/ICETOOL job that will do what you asked for. I assumed the name field can be a maximum of 12 characters. Be sure to use your Data 1 file (DOB,ID) as IN1 and your Data 2 file (ID,Name) as IN2.
Frank, the statements SPLICE, COPY those sort of ICETOOL commands wont work in our production servers. Thats the challenge in this scenario. Please dont say that "ICETOOL is a part of DFSORT". I am not sure about the reason behind this mystery.
But, here we have to go only with DFSORT commands.
Anyway, thank you very much for your prompt response.