View previous topic :: View next topic
|
Author |
Message |
sasikumar1984
Active User
Joined: 02 Jul 2007 Posts: 109 Location: Chennai - India
|
|
|
|
Hi,
I have two files as below.
File 1 Content
AAA BBB CCC
AAA EEE FFF
AAA GGG HHH
GGG HHH III
File 2 Content
XXX
YYY
ZZZ
WWW
I want my output file should be as below
AAA BBB CCC XXX
AAA BBB CCC YYY
AAA BBB CCC ZZZ
AAA BBB CCC WWW
AAA EEE FFF XXX
AAA EEE FFF YYY
AAA EEE FFF ZZZ
For each entry in file 1 should have entries for file2. Number of records in file 1 and file 2 always varies.
Is it possible to do? |
|
Back to top |
|
|
Alissa Margulies
SYNCSORT Support
Joined: 25 Jul 2007 Posts: 496 Location: USA
|
|
|
|
Sasikumar,
Try this:
Code: |
//STEP1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTOUT DD DISP=(NEW,PASS),DSN=&&TEMP1,...
//SORTIN DD *
AAA BBB CCC
AAA EEE FFF
AAA GGG HHH
GGG HHH III
//SYSIN DD *
SORT FIELDS=COPY
OUTREC OVERLAY=(12:C' ')
//*********************************
//STEP2 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTOUT DD DISP=(NEW,PASS),DSN=&&TEMP2,...
//SORTIN DD *
XXX
YYY
ZZZ
WWW
//SYSIN DD *
SORT FIELDS=COPY
OUTREC OVERLAY=(4:C' ')
//**********************************
//STEP3 EXEC PGM=SORT
//SORTJNF1 DD DISP=SHR,DSN=&&TEMP1
//SORTJNF2 DD DISP=SHR,DSN=&&TEMP2
//SORTOUT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSIN DD *
JOINKEYS FILES=F1,FIELDS=(12,1,A)
JOINKEYS FILES=F2,FIELDS=(4,1,A)
REFORMAT FIELDS=(F1:1,12,F2:1,3)
SORT FIELDS=COPY
/*
|
Steps 1 & 2 are simply inserting a control field at the end of the record, which can then be used as the JOINKEY in Step 3. |
|
Back to top |
|
|
Aaru
Senior Member
Joined: 03 Jul 2007 Posts: 1287 Location: Chennai, India
|
|
|
|
Alissa,
Quote: |
Steps 1 & 2 are simply inserting a control field at the end of the record, which can then be used as the JOINKEY in Step 3. |
In this example do we actually need the first 2 steps? Anyways the 12th and 4th column are spaces in both the input files.
I can understand that this has got somwthing to do with the input record length. Just thought of asking you. |
|
Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
|
|
|
|
Aaru,
I guess that's to generate a key in both the files which will result in a match for all the records. Unless you have something like that, you need those 2 steps. But in this particular example(instream input) I guess it's not really needed. But when it comes to "real" files, you may not have such a key in both the files which has the same value for all the records. |
|
Back to top |
|
|
Alissa Margulies
SYNCSORT Support
Joined: 25 Jul 2007 Posts: 496 Location: USA
|
|
|
|
Aaru wrote: |
In this example do we actually need the first 2 steps? |
Arun is correct. In this particular example, inserting the control field is not necessary, but I was trying to provide a jobstream that could be easily modified for "real world" situations. |
|
Back to top |
|
|
Aaru
Senior Member
Joined: 03 Jul 2007 Posts: 1287 Location: Chennai, India
|
|
|
|
Thanks Arun and Alissa for the confirmation. |
|
Back to top |
|
|
sasikumar1984
Active User
Joined: 02 Jul 2007 Posts: 109 Location: Chennai - India
|
|
|
|
Hi All,
Thanks for all u people support.... But my requirement bit got changed. i explained below.
File 1 Content
AAA BBB CCC 123 001 01
AAA BBB CCC 123 001 02
AAA EEE FFF 456 002 01
AAA GGG HHH 456 003 02
GGG HHH III 789 001 01
File 2 Content
XXX 123 001
YYY 123 001
ZZZ 123 001
XXX 123 002
YYY 123 002
YYY 456 002
WWW 456 002
YYY 789 001
I want my output file should be as below
AAA BBB CCC XXX 123 001 01
AAA BBB CCC YYY 123 001 01
AAA BBB CCC ZZZ 123 001 01
AAA BBB CCC XXX 123 001 02
AAA BBB CCC YYY 123 001 02
AAA EEE FFF YYY 456 002 01
AAA EEE FFF WWW 456 002 01
GGG HHH III YYY 789 001 01
Each entry in file 1 should have all the entries present in file having variable 2 and variable 3 in file 2 should match with variable 4 and variable 5 in file 1.
Can someone pls change the SORT card and provide me. Thanks in advance. |
|
Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
|
|
|
|
Sasikumar,
Do you want only the matching records into the output? What about the non-matching entries in both the files(not shown in your example)? |
|
Back to top |
|
|
sasikumar1984
Active User
Joined: 02 Jul 2007 Posts: 109 Location: Chennai - India
|
|
|
|
Hi Arun,
Thanks for reply. my output file should have for each entry in file 1 all the entries of file 2 having matching variable 4 and variable 5 in file 1 with variable 2 and variable 3 in file 2. If there is no match with file 2 then i dont need the that entry at all. hope this is clear. |
|
Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
|
|
|
|
Sasikumar,
You can use the below Syncsort job to achieve this.
Code: |
//STEP1 EXEC PGM=SORT
//SORTJNF1 DD DSN= File1
//SORTJNF2 DD DSN= File2
//SORTOUT DD DSN= Output file
//SYSOUT DD SYSOUT=*
//SYSIN DD *
JOINKEYS FILES=F1,FIELDS=(13,7,A)
JOINKEYS FILES=F2,FIELDS=(5,7,A)
REFORMAT FIELDS=(F1:1,12,F2:1,11,F1:20,3)
SORT FIELDS=COPY
/* |
|
|
Back to top |
|
|
sasikumar1984
Active User
Joined: 02 Jul 2007 Posts: 109 Location: Chennai - India
|
|
|
|
Hi Arun,
It worked fine for me... thanks a lot.... |
|
Back to top |
|
|
Aaru
Senior Member
Joined: 03 Jul 2007 Posts: 1287 Location: Chennai, India
|
|
|
|
Arun,
There is only one step in your final JCL |
|
Back to top |
|
|
sasikumar1984
Active User
Joined: 02 Jul 2007 Posts: 109 Location: Chennai - India
|
|
|
|
Yes Arun, only one step.. but i have a small doubt... what is the purpose of first two steps provided by Alissa first.. Please explain me. |
|
Back to top |
|
|
abhishek dadhichi
New User
Joined: 19 Apr 2007 Posts: 37 Location: Bangalore
|
|
|
|
Hi All,
I tried running the above code given by Alissa Margulies, but the following is the comppilation error.
code:
-------- PROCESSING SYSIN --------
JOINKEYS FILES=F1,FIELDS=(12,1,A)
E3 - DSS10065E - PARAMETER 'FILES' IS UNIDENTIFIED.
JOINKEYS FILES=F2,FIELDS=(4,1,A)
E4 - DSS10065E - PARAMETER 'FILES' IS UNIDENTIFIED.
REFORMAT FIELDS=(F1:1,12,F2:1,3)
SORT FIELDS=COPY
Pls let me know if this is the error due to SORT utility
Thanks,
Abhishek
[/code] |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Are you running Syncsort? If you are running Syncsort, which version? If you are not running Syncsort, you will probably not be able to use JOINKEYS.
Did you submit the job or simply run it thru some jcl checker? If you did not submit the job, do so. |
|
Back to top |
|
|
abhishek dadhichi
New User
Joined: 19 Apr 2007 Posts: 37 Location: Bangalore
|
|
|
|
Hi Dick,
Just checked in my shop ..it is DFSORT not syncsort.
Thanks for the response, dont we have similar Joinkeys facility in DFSORT..just curious.
Thanks,
Regards |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
While the sort products have many similarities, they also have quite a few differences.
If you start a new topic in the DFSORT part of the forum and describe your input files and relevant fields and what you want as output, someone may be able to show you a way to to what you need without joinkeys. |
|
Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
|
|
|
|
abhishek dadhichi wrote: |
Thanks for the response, dont we have similar Joinkeys facility in DFSORT..just curious. |
Joinkeys are the "facility" from SyncSort. |
|
Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
|
|
|
|
wrote: |
what is the purpose of first two steps provided by Alissa first.. Please explain me. |
sasikumar1984,
The purpose of those steps is already explained in this thread. Initially you tried a "keyless" matching which requires overlaying keys explicitly before matching. But your second requirement really had keys to match. |
|
Back to top |
|
|
sasikumar1984
Active User
Joined: 02 Jul 2007 Posts: 109 Location: Chennai - India
|
|
|
|
Thanks for ur reply Arun.... |
|
Back to top |
|
|
|