View previous topic :: View next topic
|
Author |
Message |
widia
New User
Joined: 04 Mar 2009 Posts: 6 Location: Chennai
|
|
|
|
Hi All,
I am new to this forum.I need to solve a issue. I have compared two files and joined them using JOINKEYS. I am facing a issue regarding this. I ve joined from column 5 to column 10.
consider 1st file
ddd 123456 name1 234
ddd 123456 name1 567
kkk 789444 name2 791
2nd file
ddd 123456 kkkk
ddd 123456 ssss
kkk 789444 rrrr
Desird output file
ddd 123456 kkkk
ddd 123456 ssss
kkk 789444 rrrr
but i am gettin output file as
ddd 123456 kkkk
ddd 123456 ssss
ddd 123456 kkkk
ddd 123456 ssss
kkk 789444 rrrr
My code is
JOINKEYS FILES=F1,FIELDS=(5,6,A)
JOINKEYS FILES=F2,FIELDS=(5,6,A)
REFORMAT FIELDS=(F1:1,11,F2:12,4)
SORT FIELDS=COPY
It is repeated four times i need to get only twice.Please help me out. |
|
Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
|
|
|
|
widia,
Hello and welcome to the forums.
The JOIN operation does a cartesian product and since you have duplicate keys in both the files, you will get the records repeated. You might need to remove duplicates from either of your input files before the JOIN operation.
From the sample data posted above, Posn 1-11 seem to be the same in both the input files. Why would you need the first 11 positions from file-1 and NOT from file-2? You need to provide a little more realistic data which covers all the possible input scenarios. |
|
Back to top |
|
|
widia
New User
Joined: 04 Mar 2009 Posts: 6 Location: Chennai
|
|
|
|
Hi arcvns,
As per my requirement, the input file can have duplicate keys. The Second input file is processed by first input file and i get additional information. So i append certain fields from first file and additional information from second file.
consider 1st file
ddd 123456 name1 234
ddd 123456 name1 567
kkk 789444 name2 791
2nd file
ddd 123456 kkkk
ddd 123456 ssss
kkk 789444 rrrr
Here 234 and 567 makes difference in first file. They are processed in second file and i get 'KKKK' for ddd 123456 name1 234 and 'ssss' for ddd 123456 name1 567 .So in my requirement keys can repeat but i need to join without repetation in output file.
Thanks in advance. |
|
Back to top |
|
|
sril.krishy
Active User
Joined: 30 Jul 2005 Posts: 183 Location: hyderabad
|
|
|
|
widia,
Either you can sort the i/p file without duplicates before join or sort the output file generated from the join to avoid the duplicates.
Thanks
Krishy |
|
Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
|
|
|
|
widia,
Will there be non-matching keys in file-1 or file-2? Your sample data has only matching records and it makes difficult to make out how your actual data would be. Also in your example, the 2nd input file and the output file are one and the same. |
|
Back to top |
|
|
widia
New User
Joined: 04 Mar 2009 Posts: 6 Location: Chennai
|
|
|
|
There will not be any non-matching keys as my first input file is processed to get second input file. The desired output file is not exactly same as second input file.It is concatenation of first and second input file. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
If the second file is data pulled from the first file, why is it needed?
If you post some more realistic and more complete "input" data (rather than alphabet soup) and post what you want as the actual output from the sample input, someone may have a suggestion. |
|
Back to top |
|
|
widia
New User
Joined: 04 Mar 2009 Posts: 6 Location: Chennai
|
|
|
|
Hi Dick,
To be more precise i ll give you the sample data,
consider file1
Rec 1: ddd 089376578 widia 574royal-st 234new-st 04/17/09
Rec 2: ddd 089376578 widia 574royal-st 234new-st 04/18/09
Rec 3: ddd 067457868 Neha 234old-st 768queen-st 04/16/09
File2
Rec 1: ddd 089376578 widia 574royal-st 234new-st reason:notmatched
Rec 2: ddd 089376578 widia 574royal-st 234new-st reason:notmatched
Rec 3: ddd 067457868 Neha 234old-st 768queen-st reason:updated
Output file
ddd 089376578 widia 574royal-st 234new-st reason:notmatched
ddd 089376578 widia 574royal-st 234new-st reason:notmatched
ddd 089376578 widia 574royal-st 234new-st reason:notmatched
ddd 089376578 widia 574royal-st 234new-st reason:notmatched
ddd 067457868 Neha 234old-st 768queen-st reason:updated
So the first file is processed to get reason in second file. Here last field date is different for the first input file.Joinkeys are perfect when the record is present once. But when it is repeated twice, i get four records in desired output file.Sorry for the inconvenience. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
I still do not understand why file 2 is created. . .
Why can file 1 not be read and the output file written as needed with the appropriate reason? |
|
Back to top |
|
|
Alissa Margulies
SYNCSORT Support
Joined: 25 Jul 2007 Posts: 496 Location: USA
|
|
|
|
Widia,
I'm with Dick on this one. How is File 2 any different than your desired output from the JOIN processing? Isn't File 2 exactly what you are already looking for? If not, then you need to provide additional details and a better example of your desired output. |
|
Back to top |
|
|
widia
New User
Joined: 04 Mar 2009 Posts: 6 Location: Chennai
|
|
|
|
Hi,
I can give u additiona info for input like this, hope this helps
File 1:
Rec 1: ddd 089376578 widia 574royal-st 234new-st 04/17/09
Rec 2: ddd 089376578 widia 574royal-st 234new-st 04/18/09
Rec 3: ddd 067457868 Neha 234old-st 768queen-st 04/16/09
File 2:
Rec 1: ddd 089376578 Error : 0000 reason:notmatched
Rec 2: ddd 089376578 Error : 0000 reason:notmatched
Rec 3: ddd 067457868 Error : 0000 reason:updated
Output file:
ddd 089376578 widia 574royal-st 234new-st reason:notmatched
ddd 089376578 widia 574royal-st 234new-st reason:notmatched
ddd 089376578 widia 574royal-st 234new-st reason:notmatched
ddd 089376578 widia 574royal-st 234new-st reason:notmatched
ddd 067457868 Neha 234old-st 768queen-st reason:updated
I need to write only reasons from second file.Hope now the issue can be solved. |
|
Back to top |
|
|
Alissa Margulies
SYNCSORT Support
Joined: 25 Jul 2007 Posts: 496 Location: USA
|
|
|
|
Here are 2 SyncSort for z/OS jobs that will do what you asked:
Code: |
//STEP1 EXEC PGM=SORT
//SORTJNF1 DD *
DDD 089376578 WIDIA 574ROYAL-ST 234NEW-ST 04/17/09
DDD 089376578 WIDIA 574ROYAL-ST 234NEW-ST 04/18/09
DDD 067457868 NEHA 234OLD-ST 768QUEEN-ST 04/16/09
//SORTJNF2 DD *
DDD 089376578 ERROR : 0000 REASON:NOTMATCHED
DDD 089376578 ERROR : 0000 REASON:NOTMATCHED
DDD 067457868 ERROR : 0000 REASON:UPDATED
//SORTOUT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSIN DD *
JOINKEYS FILES=F1,FIELDS=(5,9,A)
JOINKEYS FILES=F2,FIELDS=(5,9,A)
REFORMAT FIELDS=(F1:1,50,F2:27,18)
INREC OVERLAY=(69:SEQNUM,8,ZD,RESTART=(1,68))
SORT FIELDS=COPY
OUTFIL INCLUDE=(69,8,ZD,EQ,1),BUILD=(1,41,51,18)
/* |
or
Code: |
//SYSIN DD *
JOINKEYS FILES=F1,FIELDS=(5,9,A)
JOINKEYS FILES=F2,FIELDS=(5,9,A)
REFORMAT FIELDS=(F1:1,50,F2:27,18)
SORT FIELDS=(1,68,CH,A)
DUPKEYS FIELDS=NONE
OUTREC FIELDS=(1,41,51,18)
/* |
Here is the output that is produced from both of the above applications:
Code: |
DDD 067457868 NEHA 234OLD-ST 768QUEEN-ST REASON:UPDATED
DDD 089376578 WIDIA 574ROYAL-ST 234NEW-ST REASON:NOTMATCHED
DDD 089376578 WIDIA 574ROYAL-ST 234NEW-ST REASON:NOTMATCHED
|
|
|
Back to top |
|
|
widia
New User
Joined: 04 Mar 2009 Posts: 6 Location: Chennai
|
|
|
|
Thanks Alissa Margulies.. it is working fine. Thanks a lot.. |
|
Back to top |
|
|
|