I am facing a problem when working with joins. Let me tell my requirement first
Requirement: To compare two files based on a Key and add the code present in last 2 columns of one file to other file
Here is the code which i am using
LRECL of F1 is 569
LRECL of F2 is 355
Code:
JOINKEYS FILE=F1,FIELDS=(9,7,A) --> First file Key is in 9th position
JOINKEYS FILE=F2,FIELDS=(12,7,A) --> Second file Key is in 12th Position
JOIN UNPAIRED,F1 --> Wanted only Paired records from F1 as well as Unpaired from F1
REFORMAT FIELDS=(F1:1,569,F2:1,355),FILL=X'FF'
SORT FIELDS=(1,567,CH,A) --> Trying to eliminate Duplicates if any
SUM FIELDS=NONE
INREC IFTHEN=(WHEN=((1,1,BI,NE,X'FF'),AND,(9,7,CH,EQ,581,7,CH)),
BUILD=(1:1,567,568:923,2)),--> adding the last 2 digits from F2 to F1 if a match is found
IFTHEN=(WHEN=NONE,
BUILD=(1:1,569))--> Write the record as it is from F1 when no match is found
Now i had a scenario where F2 is having 2 entries for a particular Key (say abcdefg) One record having code in last 2 columns and another one having spaces
After matching is done i am unable to retrieve the code to the last 2 columns of F1, Since it is picking the second record of F2 where the last 2 columns is blank
Simple example for better understanding:
F1
Code:
ab spaces
cd spaces
ef spaces
F2
Code:
ab 01
ab spaces
cd 02
ef 03
After matching i should get the following result
Code:
ab 01
cd 02
ef 03
But My output file is looking like
Code:
ab spaces --> i want the code to be added
cd 02
ef 03
Note: F1 is having a Unique record for Particular Key
F2 may have more than one record for a particular key
Its like One to Many mapping
Please help me in this requirement. Correct me if i was going wrong some where
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
OK. I still have no clue of what you actually want. You don't want space? Is that it?
If so, put OMIT COND=(start,2,CH,EQ,C' ') in JNF2CNTL.
If you only want one record for your output, and it doesn't matter which because they both have 01, use IFTHEN=(WHEN=GROUP for your key, PUSH a sequence number, and on OUTFIL INCLUDE=(sequence number position,length,ZD,EQ,1) or similar.
SORTing just so you can use SUM FIELDS=NONE is wasteful of resources.
JOINKEYS FILE=F1,FIELDS=(9,7,A)
JOINKEYS FILE=F2,FIELDS=(12,7,A)
JOIN UNPAIRED,F1
REFORMAT FIELDS=(F1:1,569,F2:1,355),FILL=X'FF'
SORT FIELDS=COPY
OUTREC IFTHEN=(WHEN=GROUP,BEGIN=((1,1,BI,NE,X'FF'),
AND,(9,7,CH,EQ,581,7,CH)),
PUSH=(568:921,2)), --> trying to group the matched records and push the last 2 columns from file 2 to file 1
IFTHEN=(WHEN=GROUP,BEGIN=((1,1,BI,NE,X'FF'),
AND,(570,1,BI,EQ,X'FF')),
PUSH=(1:1,569))-->when there is no match in file 2 found write the file 1 record as it is
OUTFIL BUILD=(1,569)
But unfortunately i was unable to get the result
Here is an simple example for better understanding. Assume the key is first 2 charcters i.e (ad, ef, and hi respectively)
abcd 01 --> for one ab record in file 1 there are 4 matches in file2
abcd 01
abcd 01
abcd spaces
efgh 02--> for one ef record in file1 there are 2 matches in file2
efgh 02
hijk spaces --> no matches hence writing record as it is
Final output should be like:
Code:
abcd 01
efgh 02
hijk spaces
My ultimate goal is to update the file 1 with the codes present in file 2 in the last 2 columns for matching key
If there is no match then write the record as it is
Main problem is occurring in following cases
1.When the matched record is having a value in last 2 columns
2.When the same matched record is also having spaces in last 2 columns
In such scenario i was getting spaces in my output file
Assume
File1 - 20 records
File2 - 30 records
Matching in both - 10 records
Now my output file count should be - 20 records (same as file1) with 10 records having the code at last taken from file 2
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
I'm going to assume for now that you have SyncSort. If you do have DFSORT, post the sysout from the step so we can see your software level.
SyncSort questions lives in the JCL forum.
I have Code'd your post, again. Please learn how to use the Code tags.
It seems you have no use of the records with space from File 2. So, add OMIT=(start,2,CH,EQ,C' ') to your JOINKEYS for F2.
You (SyncSort) don't have KEYBEGIN. Your BEGIN is making a group for every record.
Use IFTHEN=(WHEN=INIT to append a sequence number with RESTART=(9,7). For now, as you only seem to have a single value or space on the F2, forget the GROUP.
For OUTFIL, use INCLUDE=( to only keep those with a sequence number of one.
