Join keys with matched values



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

Join keys with matched values

Postby tjegan » Tue Nov 11, 2014 7:06 pm

Hi All,
I want to compare two input files and matching keys from both the files should be written into the output file.
But the input file can have duplicate values in file1 as well as file2. The below code works fine if we have duplicate values from
the input file1 alone.
Key column is: 1,4

//SYSIN    DD *                                                       
  SORT FIELDS=COPY                                                   
  JOINKEYS FILE=F1,FIELDS=(1,4,A)                                     
  JOINKEYS FILE=F2,FIELDS=(1,4,A)                                     
  REFORMAT FIELDS=(F1:1,80,F2:1,80)                                   
   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))                                 
/*   


Input File1
1000 ARUN A
1001 BALA B
1001 BALA D
1001 AAAA D
1001 BBBB D
1007 CHAN C

Input file2:
1001 BALA 1     
1003 KATE 2     
1001 JOHN 5     
1004 EDWIN 3     

Output:
1001 BALA B 
1001 BALA 1 
1001 BALA B 
1001 BALA D 
1001 BALA D 
1001 AAAA D 
1001 AAAA D 
1001 BBBB D 
1001 BBBB D 

Expected output:
1001 BALA B
1001 BALA D
1001 AAAA D
1001 BBBB D
1001 BALA 1     
1001 JOHN 5 

Can anyone please tell me where to modify to control card to get the desired result. Or is there any alternate way to get the expected output using JCL?

Thanks much.
tjegan
 
Posts: 33
Joined: Mon Mar 25, 2013 5:58 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Join keys with matched values

Postby NicC » Tue Nov 11, 2014 7:28 pm

Certainly cannot be done with JCL as that is not the kind of thing that JCL is designed to do. Note: this is because JCL is used to tell the operating system which programs you want to run and the resources required by those programs. Programs, utility or otherwise, are not JCL although JCL may be required to get the programs executed.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: Join keys with matched values

Postby tjegan » Tue Nov 11, 2014 7:38 pm

Thanks for the clarification. I will check for programming logic for the above.
tjegan
 
Posts: 33
Joined: Mon Mar 25, 2013 5:58 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Join keys with matched values

Postby Aki88 » Fri Nov 14, 2014 11:23 am

Hello Tjegan,

You can also refer this link for example.

Hth.
Aki88
 
Posts: 381
Joined: Tue Jan 28, 2014 1:52 pm
Has thanked: 33 times
Been thanked: 36 times

Re: Join keys with matched values

Postby srikanth_cool » Sun Nov 16, 2014 12:04 pm

Hi,
I have two input flat files(FB) with different record length. I need to get only the unique records from particular positions.

Input File 1:
100 AAA
200 BBB

Input File 2:
ABC ZZZ 100 QQQ AAA
ABC XXX 300 QQQ CCC

Need Output like below:
100 AAA
200 BBB
300 CCC

How can we achieve the above result using sort. Please help

Thanks in Advance
srikanth_cool
 
Posts: 2
Joined: Sun Nov 16, 2014 11:36 am
Has thanked: 0 time
Been thanked: 0 time

Re: Join keys with matched values

Postby Aki88 » Mon Nov 17, 2014 12:06 pm

Hello Srikanth,

Please start new thread for queries, kindly avoid tailgating a post.

Try checking the link shared in the previous post to 'Tjegan', it'll take you to the DFSORT Application Programming Guide, there are various examples shared for JOINKEYS in the manual that'll guide you to code a SORT card for your requirement.
In case your solution doesn't work, please post your SORT card along with the error, and some one will be able to guide you further.

Regards.
Aki88
 
Posts: 381
Joined: Tue Jan 28, 2014 1:52 pm
Has thanked: 33 times
Been thanked: 36 times


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post