My requirement is to create 3 output files.
1) File01 records which donot have match in File02
2) File02 records which donot have match in File01
3) Matched content based on joinkey (6,15,CH,A)
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
For Syncsort (please post in the correct forum next time) you have to rely on the absence of data to identify the mismatches.
So, pick a field which can't have the default FILL value, test for that value.
Code:
FILL DATA
DATA FILL
DATA DATA
If field in first file is FILL, output second file.
If field in second file is FILL, output first file.
If neither of the above, output two records to third file.
1. Did you try Bill's suggestion/solution?
2. You mentioned you got incorrect output. Please post what output you got. Also, what is inside SORTJNF1 and SORTJNF2?
3. Suggest (I hope not to mess up this time) you read the manual (since you mentioned you are new to joinkeys). You will get a clear understanding of the constructs/syntax/keywords/options used and I am sure you can then deduce what you want.
My requirement is to create 3 output files.
1) File01 records which donot have match in File02
2) File02 records which donot have match in File01
3) Matched content based on joinkey (6,15,CH,A)
Please let me know in case you need information from my end.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
You are using one byte at position 60 in the REFORMAT record to decide on file 1/2, and you are using a particular value for that position.
If you have an unmatched file 1, then the whole 1,57 for file two will be space (or binary zeros, or whatever, depending on your installation).
If you have unmatched file 2, then the whole 1,57 for file one will be, as above.
If you have a match, both 1,57s will have data.
If you have a particular byte which cannot contain the FILL value used for unmatched records on the JOINKEYS then you can test that byte. At worst you can test the whole record as Pandora-box has already suggested.
There are examples in the JCL forum. Search for JOINKEYS and FILL.
Here you would have faced SYNTAX error, as "?" in the SORT card is not supported by SYNCSORT. Please understand all the DFSORT syntax wouldnt work for SYNCSORT
As per my experience in SYNCSORT I don't think you can get all the 3 outputs using 1 Sortcard. You will have to use 3 Sortcards (i.e. you will require 3 steps) 1 for each file.
To get F1 unmatched records
JOINKEYS FILES=F1,FIELDS=(6,15,A)
JOINKEYS FILES=F2,FIELDS=(6,15,A)
JOIN UNPAIRED,F1,ONLY
To get F2 unmatched records
JOINKEYS FILES=F1,FIELDS=(6,15,A)
JOINKEYS FILES=F2,FIELDS=(6,15,A)
JOIN UNPAIRED,F2,ONLY
And 3rd 1 without the UNPAIRED to get the match records.
Pandora, I agree ou have better understanding than me. But I'm suggesting as I had faced the same issue before.
I agree that JOIN UNPAIRED F1,F2 IS LIKE FULL OUTER JOIN of SQL and in DFSORT you have the ability to identify the
1. Unmatched F1 records
2. Unmatched F2 records and
3. F1 F2 matched records
with just 1 sortcard with the help of '?' in it, which would be the last byte in your REFORMAT record. But this kind of functionality is not available SYNCSORT. The DFSORT example is explained here
Check first 57 ne Space and next 57 bytes Eq Space
Second input file
check opposite of previous check
Third file
Add SAVE before build in your outfil
Bill Woodger wrote:
You are using one byte at position 60 in the REFORMAT record to decide on file 1/2, and you are using a particular value for that position.
If you have an unmatched file 1, then the whole 1,57 for file two will be space (or binary zeros, or whatever, depending on your installation).
If you have unmatched file 2, then the whole 1,57 for file one will be, as above.
If you have a match, both 1,57s will have data.
If you have a particular byte which cannot contain the FILL value used for unmatched records on the JOINKEYS then you can test that byte. At worst you can test the whole record as Pandora-box has already suggested.
There are examples in the JCL forum. Search for JOINKEYS and FILL.
bhavin.mehta,
Nobody is 'backfiring' you. But, the previous posts implied what you mentioned in your post. So as Bill mentioned you didn't test (possibly) for FILL character in Syncsort.
Joined: 05 Jan 2007 Posts: 101 Location: chennai (India)
Quote:
As per my experience in SYNCSORT I don't think you can get all the 3 outputs using 1 Sortcard. You will have to use 3 Sortcards (i.e. you will require 3 steps) 1 for each file.
.jpg)
