I have a requirement to extract the data from 2 files with matching keys from one file (File 2) and non matching keys from both the files (File 1 and File 2).
Data from file 1:
Code:
A 101
C <spaces>
B 102
E 105
D 104
Data from File 2:
Code:
P 301
A 200
Q 302
B 101
D 109
Z 901
I would like to extract the rec from File 2 if there is a match on Col1 and also extract non matching recs from both the files.
Sample output file:
Code:
A 200
B 101
C <spaces>
D 109
E 105
P 301
Q 302
Z 901
Please help to extract the above output using SORT.
Thanks.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
mistah kurtz,
The matching marker can only have one of three values. IFTHEN=(WHEN=NONE will give you an "ELSE", which will make it clearer, rather than have the next person along wondering what values are not being catered for.
maki_psg,
Are your input files in sequence on the first byte? Are duplicates possible on either file?
Your volumes must be small, so you'd probably not notice, but you might want to look at using MERGE.
Specify your second file as the first input to the MERGE and use OPTION EQUAL and SUM FIELDS=NONE.
EDIT: I can see from your sample data that your files are not in sequence. Forget MERGE.
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
mistah kurtz,
Good Job. I would like to tidy up a little bit more.
You don't have to specify F1 and F2 on the JOIN statement. A simple JOIN UNPAIRED will give you matched as well unmatched records from both F1 and F2.
You also don't need to build the record once again when the indicator is '1' as the data is already present in positions 1 thru 80. You can use IFOUTLEN=80, to make sure we got the right LRECL for output.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
If the volumes were large, there might be advantages to rearranging things.
If most of the records were "matches" with only a few "mismatches" then F1 and F2 could be "swapped", so that most of the input data required for output are in the first part of the REFORMAT record, and processed simply by the IFOUTLEN. This goes for whatever combination gives the preponderance of records. If should be the first part of the REFORMAT record.
I know there is a thing about "negation", but with only three possibilities, NE,C'1' might do :-)
Good Job. I would like to tidy up a little bit more.
You don't have to specify F1 and F2 on the JOIN statement. A simple JOIN UNPAIRED will give you matched as well unmatched records from both F1 and F2.
You also don't need to build the record once again when the indicator is '1' as the data is already present in positions 1 thru 80. You can use IFOUTLEN=80, to make sure we got the right LRECL for output.
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
maki_psg wrote:
@ Pandora, We are using SYNCSORT.
maki_psg
Please post queries related to Syncsort in the JCL part of this forum in future. All the DFSORT keywords may not work well in Syncsort and vice versa, though for the most part, both will work the same way.
btw, Could you tell your Syncsort version, my manual does not say anything about the '?' support.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
1.4.1.0 has undocumented support for JNFnCNTL files. Perhaps it also has for "?"? Either that or, unusually, TS/OP thinks they have SyncSort but don't....
maki_psg,
If your data is huge, pay attention to the points already mentioned. Mock-up your huge amounts, see if you get a reasonable difference in resources used, and choose the option which best fits your data.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
maki_psg,
You have a previous topic, from early April, which you also posted in DFSORT.
Please do not do that again. SyncSort questions go in the JCL part of the forum.
From that topic you say "I am currently using SYNCSORT FOR Z/OS 1.4.1.0R." Can you confirm that the "?" works in the Control Cards that you are using, please?
You have a previous topic, from early April, which you also posted in DFSORT.
Please do not do that again. SyncSort questions go in the JCL part of the forum.
From that topic you say "I am currently using SYNCSORT FOR Z/OS 1.4.1.0R." Can you confirm that the "?" works in the Control Cards that you are using, please?
Bill, apologies for posting the topic incorrectly .
Yes, "?" works in the control cards which were posted at the top.
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
Hello,
If your organization is registered with Syncsort, they send informational e-mails with some regularity . . . Make sure that whoever recieves them sends them to those who use Syncsort.
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
Hello,
Quote:
Not sure why the 1.4 documentation does not mention about this enhancement
It is in my 1.4 doc ~
Quote:
The join facility, controlled by the JOINKEYS, JOIN, and REFORMAT control statements, joins records from two source files
.
.
.
Specification of the placement of the data fields within the record created by the join operation is provided through the REFORMAT control statement
Would be pretty happy if get a chair to sit near those experts and influence my knowledge so that I could test & answer SYNCSORT queries like Arul does
But attimes I wonder how person could know so much things so that he writes a big manual
Yes Alissa is helpful once I mailed her when I was using SYNCSORT in my shop she very immediately helped with manuals
Ofcourse all should be providing those info which she asks for
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
Quote:
It is in my 1.4 doc ~
Hi dick,
I am afraid my 1.4 manual does not have the "?" as well as "JNFnCNTL" support details. I had sent an email to Syncsort support asking for any documentation and they responded saying that a ZAP is available to add these enhancements to even 1.3.2, but nothing on the documentation part.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
Unfortunately, the thing about "undocumented features" is that they don't appear in the manual. I'd seen a couple of references elsewhere so asked Alissa about the JNFnCNTLs. I didn't know about the ?, and don't know if it is in the "patch", or only available above 1.4.0.
.jpg)
.
, Not sure why the 1.4 documentation does not mention about this enhancement. 
