Help required in Join keys



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

Help required in Join keys

Postby tjegan » Wed Oct 22, 2014 1:36 pm

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.
tjegan
 
Posts: 34
Joined: Mon Mar 25, 2013 5:58 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Help required in Join keys

 

Re: Help required in Join keys

Postby BillyBoyo » Wed Oct 22, 2014 4:46 pm

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.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: Help required in Join keys

Postby tjegan » Wed Oct 22, 2014 8:48 pm

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
tjegan
 
Posts: 34
Joined: Mon Mar 25, 2013 5:58 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Help required in Join keys

Postby BillyBoyo » Wed Oct 22, 2014 8:57 pm

 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.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: Help required in Join keys

Postby tjegan » Wed Oct 22, 2014 9:39 pm

Thanks a ton for your quick response. I will try with the above statement.
tjegan
 
Posts: 34
Joined: Mon Mar 25, 2013 5:58 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Help required in Join keys

Postby tjegan » Wed Oct 29, 2014 3:30 pm

Thanks much for the suport. the above code is working fine
tjegan
 
Posts: 34
Joined: Mon Mar 25, 2013 5:58 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Help required in Join keys

Postby tjegan » Fri Oct 31, 2014 5:46 pm

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   
tjegan
 
Posts: 34
Joined: Mon Mar 25, 2013 5:58 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Help required in Join keys

Postby BillyBoyo » Fri Oct 31, 2014 7:44 pm

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.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: Help required in Join keys

Postby tjegan » Mon Nov 03, 2014 12:06 am

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.
tjegan
 
Posts: 34
Joined: Mon Mar 25, 2013 5:58 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Help required in Join keys

Postby BillyBoyo » Mon Nov 03, 2014 12:44 am

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))
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Next

Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post