Page 1 of 1

Join keys with matched values

PostPosted: Tue Nov 11, 2014 7:06 pm
by tjegan
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.

Re: Join keys with matched values

PostPosted: Tue Nov 11, 2014 7:28 pm
by NicC
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.

Re: Join keys with matched values

PostPosted: Tue Nov 11, 2014 7:38 pm
by tjegan
Thanks for the clarification. I will check for programming logic for the above.

Re: Join keys with matched values

PostPosted: Fri Nov 14, 2014 11:23 am
by Aki88
Hello Tjegan,

You can also refer this link for example.

Hth.

Re: Join keys with matched values

PostPosted: Sun Nov 16, 2014 12:04 pm
by srikanth_cool
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

Re: Join keys with matched values

PostPosted: Mon Nov 17, 2014 12:06 pm
by Aki88
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.