Page 1 of 2

Help required in Join keys

PostPosted: Wed Oct 22, 2014 1:36 pm
by tjegan
Record length of all the two input files are 80 and the key starts from the 1st position with the length 4. I want to display the matching records from both the input files into the output files like the below.
I tried in vain using join keys with reformat option but did not get the expected result. Can anyone please provide the sample code for this.


Key: 1 to 4 bytes

File1:
1000 Arun A
1001 Bala B
1007 Chan C

File2:
1001 Bala 1
1003 Kate 2
1004 Edwin 3


Output file
1001 Bala B
1001 Bala 1

Many thanks in Advance.

Re: Help required in Join keys

PostPosted: Wed Oct 22, 2014 4:46 pm
by BillyBoyo
Can you show what you tried, the data you used, and the results you received? Please try to use the Code tags to preserve spacing for code and data. In the Full Editor (or after POSTREPLY) highlight the code/data and click on the Code button. Use the Preview button to check how the post will look, then Submit when it looks how you want.

Re: Help required in Join keys

PostPosted: Wed Oct 22, 2014 8:48 pm
by tjegan
Sorry for the inconvenience. Since I am on vacation unable to send all the error report. But the source look likes the below. Here I replaced reformat statement like anything in a trial and error method but no luck. I want to copy the matched records from both the input files. So can you please suggest the expected REFORMAT statement to meet the above condition.

JOINKEYS FILE=F1,FIELDS=(1,4,A)
JOINKEYS FILE=F2,FIELDS=(1,4,A)
 JOIN UNPAIRED,F1,F2
 [b]REFORMAT FIELDS=(F1:1,80,F2:1,80)[/b]
 OPTION COPY

Re: Help required in Join keys

PostPosted: Wed Oct 22, 2014 8:57 pm
by BillyBoyo
 JOINKEYS FILE=F1,FIELDS=(1,4,A)
 JOINKEYS FILE=F2,FIELDS=(1,4,A)
 REFORMAT FIELDS=(F1:1,80,F2:1,80)
 OPTION COPY


With no JOIN statement, you will only get the matches.

With matches-only the REFORMAT is optional, but clearer to be explicit.

You want to output two records, and the only way you can get two from one is with the slash operator "/" on OUTFIL BUILD (ignore OUTFIL OUTREC please).

 OUTFIL BUILD=(1,80,
               /,
               81,80)


If you put those together, you should get what you want. If not, show what you do get, with your input, and what you expect.

Re: Help required in Join keys

PostPosted: Wed Oct 22, 2014 9:39 pm
by tjegan
Thanks a ton for your quick response. I will try with the above statement.

Re: Help required in Join keys

PostPosted: Wed Oct 29, 2014 3:30 pm
by tjegan
Thanks much for the suport. the above code is working fine

Re: Help required in Join keys

PostPosted: Fri Oct 31, 2014 5:46 pm
by tjegan
Sorry for the inconvenience.
I used the same code for duplicate records and got the below result.
If we have duplicate key value then we will not be getting the expected result.
Is there a way to get the expected result even though we have duplicate records from the input files as given below ?.

USERID.INPUT.FILE2
----+----1----+-
1000 ARUN A     
1001 BALA B     
1001 BALA D     
1007 CHAN C     

USERID.INPUT.FILE1
----+----1----
1001 BALA 1   
1003 KATE 2   
1004 EDWIN 3 

USERID.OUTPUT.FILE
1001 BALA 1   
1001 BALA B   
1001 BALA 1   
1001 BALA D   

Expected output:
1001 BALA 1   
1001 BALA B   
1001 BALA D   

Re: Help required in Join keys

PostPosted: Fri Oct 31, 2014 7:44 pm
by BillyBoyo
Yes, with a match on duplicate keys your will get a REFORMAT record for each match.

Put it this way. SORT will give you all the possible combinations. If you want fewer than that, you have to code for it yourself.

If you extend your record temporarily to add a sequence number with a RESTART= for your key on the file which cannot contain duplicates, then you can adjust your OUTFIL coding so that the pair of records are only output when the sequence number is one. Otherwise, just output the record from the multiple data.

Re: Help required in Join keys

PostPosted: Mon Nov 03, 2014 12:06 am
by tjegan
Can you please let me know where i need to mention the restart and sequence number from the above code to get the desired output(3 records as mentioned above). I am new to the DFSORT and i read the manuals from the GOOGLE still i am not getting where to use the parameter(RESTART) for the above scenario.

Re: Help required in Join keys

PostPosted: Mon Nov 03, 2014 12:44 am
by BillyBoyo
Something like the following. Ensure that the SEQNUM is a reasonable size for your data (I used one digit more than is "possible"). The RESTART will start the SEQNUM from its initial value (default is one) when the key (1,4 here) changes. The code should then output two records for the first REFORMAT of a key, and after that only the positions 1,80 from the REFORMAT record.

 OUTFIL IFTHEN=(WHEN=INIT,
                     OVERLAY=(161:SEQNUM,4,ZD,RESTART=(1,4))),
        IFTHEN=(WHEN=(161,4,CH,EQ,C'0001'),
                      BUILD=(1,80,
                             /,
                             81,80)),
        IFTHEN=(WHEN=NONE,
                      BUILD=(1,80))