View previous topic :: View next topic
|
Author |
Message |
bodatrinadh
Active User
Joined: 05 Jan 2007 Posts: 101 Location: chennai (India)
|
|
|
|
Hi All,
I've two files A , B and consider both the files are having duplicates. File A is compared with first seven fields in File B and the common has to written to below OUTPUT result. When i tried with Joinkeys its going for Cross Merging and giving 7*35 records in output file.
Please find the below example
File A File B Output
1000800 1000800001 1000800 1000800001
1000800 1000800002 1000800 1000800002
1000800 1000800003 1000800 1000800003
1000800 1000800004 1000800 1000800004
1000800 1000800005 1000800 1000800005
1000800 1000800006 1000800 1000800006
1000800 1000800007 1000800 1000800007
1000801 1000800008 1000801 1000801035
1000800009
1000800010
1000800011
1000800012
1000800013
1000800014
1000800015
1000800016
1000800017
1000800018
1000800019
1000800020
1000800021
1000800022
1000800023
1000800024
1000800025
1000800026
1000800027
1000800028
1000800029
1000800030
1000800031
1000800032
1000800033
1000800034
1000800035
1000801035
Please advice regarding the same.
Thanks
Trinadh |
|
Back to top |
|
|
Binop B
Active User
Joined: 18 Jun 2009 Posts: 407 Location: Nashville, TN
|
|
|
|
Hi Trinadh
To be honest, I did not understand the sample you have provided...
could you please structure it a little more better
Probably like
File A
Code: |
---- ---- -----
---- ---- -----
---- ---- ----- |
File B
Code: |
---- ---- -----
---- ---- -----
---- ---- ----- |
Expected Output
Code: |
---- ---- -----
---- ---- -----
---- ---- ----- |
|
|
Back to top |
|
|
bodatrinadh
Active User
Joined: 05 Jan 2007 Posts: 101 Location: chennai (India)
|
|
|
|
The previous one was copied from the excel. guess only the lines are appearing! This time i copied from the textpad. PLease let me know if you are facing issue in viewing the data.
File A
1000800
1000800
1000800
1000800
1000800
1000800
1000800
1000801
File B
1000800001
1000800002
1000800003
1000800004
1000800005
1000800006
1000800007
1000800008
1000800009
1000800010
1000800011
1000800012
1000800013
1000800014
1000801035
Output :-
1000800 1000800001
1000800 1000800002
1000800 1000800003
1000800 1000800004
1000800 1000800005
1000800 1000800006
1000800 1000800007
1000801 1000801035
Thanks
Trinadh |
|
Back to top |
|
|
sqlcode1
Active Member
Joined: 08 Apr 2010 Posts: 577 Location: USA
|
|
|
|
bodatrinadh,
Quote: |
When i tried with Joinkeys its going for Cross Merging and giving 7*35 records in output file. |
It did exactly what you asked for. It produced all the possible combinations for matching records. Cartesian Product.
Please explain the rules for getting output. In your expected output, how do you limit your expected output to only 7 records for matching key from file2? What about matching key from 1000800008 through 1000800014? Where did they go?
Thanks, |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Quote: |
PLease let me know if you are facing issue in viewing the data. |
Look at the sample Binop has provided. This is accomplished using the "Code" tag.
When you post data, jcl, code, etc use copy/paste and the "Code" tag to preserve alignment. We have Preview so you can see your post as it will appear to the forum (rather than how things look in the Reply Editor). When the appearance is as you want, Submit.
You need to explain the rules of which records ahould be "kept" and which should be discarded. When desired output is shown, the rules that cause that output need to be posted. |
|
Back to top |
|
|
bodatrinadh
Active User
Joined: 05 Jan 2007 Posts: 101 Location: chennai (India)
|
|
|
|
Hi,
Here is the continuation to my previous post..
I've two files A , B and consider both the files are having duplicates. File A is compared with first seven fields in File B and the common has to written in to OUTPUT. I don't want the output in cartesian product. It should be one to one matching..
FILE-A :-
1000618
1000628
1000635
1000636
1000650
1000651
1000652
1000653
1000654
1000800
1000800
1000800
1000800
1000800
1000800
1000800
File B:-
1000635004
1000635005
1000635006
1000636001
1000636002
1000636003
1000636004
1000636005
1000637001
1000638001
1000650001
1000650002
1000650003
1000651001
1000651002
1000652001
1000652002
1000653001
1000654001
1000800001
1000800002
1000800003
1000800004
1000800005
1000800006
1000800007
1000800008
1000800009
1000800010
1000800011
1000800012
1000800013
1000800014
1000801035
Output should be:-
1000635 1000635004
1000636 1000636001
1000650 1000650001
1000651 1000651001
1000652 1000652001
1000653 1000653001
1000654 1000654001
1000800 1000800001
1000800 1000800002
1000800 1000800003
1000800 1000800004
1000800 1000800005
1000800 1000800006
1000800 1000800007 |
|
Back to top |
|
|
Binop B
Active User
Joined: 18 Jun 2009 Posts: 407 Location: Nashville, TN
|
|
|
|
May I ask - Why do you want to do this using DFSORT rather than a COBOL program.
Anyway, to my understanding - I dont think this is possible just by using DFSORT especially since you have the "File A is compared with first seven fields in File B" rule.... |
|
Back to top |
|
|
smijoss
Active User
Joined: 30 Aug 2007 Posts: 114 Location: pune
|
|
|
|
the requirement seems so simple, have you even tried reading JOINKEYS ?
or you simply need the code ?
Quote: |
I don't want the output in cartesian product. It should be one to one matching.. |
you can achieve this through JNF1CNTL |
|
Back to top |
|
|
sqlcode1
Active Member
Joined: 08 Apr 2010 Posts: 577 Location: USA
|
|
|
|
bodatrinadh,
Do you have JOINKEYS ptf installed at your shop? Please provide RECFM and LRECL for both input(s) and output files.
Thanks, |
|
Back to top |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
|
|
|
|
Bodatrinadh,
Here's a DFSORT JOINKEYS job that will do what you asked for. I assumed your input files have RECFM=FB and LRECL=80, but the job can be changed appropriately for other attributes.
Code: |
//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//IN1 DD DSN=... input file1 (FB/80)
//IN2 DD DSN=... input file2 (FB/80)
//SORTOUT DD DSN=... output file (FB/18)
//SYSIN DD *
JOINKEYS F1=IN1,FIELDS=(1,7,A,81,8,A),SORTED
JOINKEYS F2=IN2,FIELDS=(1,7,A,81,8,A),SORTED
REFORMAT FIELDS=(F1:1,8,F2:1,10)
OPTION COPY
/*
//JNF1CNTL DD *
INREC OVERLAY=(81:SEQNUM,8,BI,RESTART=(1,7))
/*
//JNF2CNTL DD *
INREC OVERLAY=(81:SEQNUM,8,BI,RESTART=(1,7))
/*
|
|
|
Back to top |
|
|
bodatrinadh
Active User
Joined: 05 Jan 2007 Posts: 101 Location: chennai (India)
|
|
|
|
Thanks Frank,
Can i use above code for files having different LRECL.
The first file (F1) is of 137 where the key starts at position 1 and length 7 and for File F2 the key starts at 3rd position of length 7 and output is of 150 record length (1,137 from file F1 + at 141 position 10 char field from file F2)
for the matched records i'm adding the 10 digit/char key at last.
Thanks
Trinadh |
|
Back to top |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
|
|
|
|
Quote: |
Can i use above code for files having different LRECL. |
Yes.
Quote: |
The first file (F1) is of 137 where the key starts at position 1 and length 7 and for File F2 the key starts at 3rd position of length 7 and output is of 150 record length (1,137 from file F1 + at 141 position 10 char field from file F2)
for the matched records i'm adding the 10 digit/char key at last. |
I don't understand this description. What is the LRECL of each file?
What is the starting position and length of the key in each file?
What do you want for the output record exactly?
If you want to attempt to figure it out yourself, complete details on DFSORT's JONKEYS function (with examples) can be found at:
www.ibm.com/support/docview.wss?rs=114&uid=isg3T7000174 |
|
Back to top |
|
|
bodatrinadh
Active User
Joined: 05 Jan 2007 Posts: 101 Location: chennai (India)
|
|
|
|
Apologize for the confusion caused, there is a typo error in my previous post..
Please find the file description:-
File-1 (F1) - the record length is 137 and the key starts at position 1 of length 7
File-1 (F2) - the record length is 496 and the key starts at position 3 of length 10 but we required first 7 char for matching.
The output file is of 150 record length = (137 from file F1 + at 141 position 10 char field from file F2)
Thanks
Trinadh |
|
Back to top |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
|
|
|
|
If I understand what your data looks like and what you want to do (and I'm not sure I do), then this DFSORT job will do it:
Code: |
//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//IN1 DD DSN=... input file1 (FB/137)
//IN2 DD DSN=... input file2 (FB/496)
//OUT DD DSN=... output file (FB/150)
//SYSIN DD *
JOINKEYS F1=IN1,FIELDS=(1,7,A,497,8,A),SORTED
JOINKEYS F2=IN2,FIELDS=(3,7,A,497,8,A),SORTED
REFORMAT FIELDS=(F1:1,137,F2:3,10)
OPTION COPY
OUTFIL FNAMES=OUT,BUILD=(1,137,141:138,10)
/*
//JNF1CNTL DD *
INREC OVERLAY=(497:SEQNUM,8,BI,RESTART=(1,7))
/*
//JNF2CNTL DD *
INREC OVERLAY=(497:SEQNUM,8,BI,RESTART=(3,7))
/*
|
|
|
Back to top |
|
|
|