Page 1 of 1

Sorting on common fields with duplicates in the input files.

PostPosted: Tue Aug 18, 2009 11:54 pm
by Abhijat
Hi,
I have following 2 files:
File1: Fields (X,A,B) File is sorted on the basis of the fields A and B asc
LLLLLL AAAAAA 00000000000000000000000000
LLLLLL AAAAAA 11111111111111111111111111
MMMMMM AAAAAA 11111111111111111111111111
OOOOOO BBBBBB 00000000000000000000000000
PPPPPP BBBBBB 33333333333333333333333333
NNNNNN CCCCCC 00000000000000000000000000

File2: Fields (A,B,Z) File is sorted on the basis of the fields A and B asc
AAAAAA   11111111111111111111111111   DATA01
AAAAAA   11111111111111111111111111   DATA02
AAAAAA   11111111111111111111111111   DATA03
AAAAAA   22222222222222222222222222   DATA04
BBBBBB   00000000000000000000000000   DATA05
BBBBBB   00000000000000000000000000   DATA06
BBBBBB   11111111111111111111111111   DATA07
BBBBBB   22222222222222222222222222   DATA08
CCCCCC   00000000000000000000000000   DATA09
CCCCCC   11111111111111111111111111   DATA11
CCCCCC   22222222222222222222222222   DATA12


Final output file expected as:
LLLLLL AAAAAA 11111111111111111111111111
MMMMMM AAAAAA 11111111111111111111111111
OOOOOO BBBBBB 00000000000000000000000000
NNNNNN CCCCCC 00000000000000000000000000


Now I need to get an output file with only the details from the file1 which have a matching values for the fields A and B from both the files. Please note that both the files can have duplicates on the joining fields. I will need to have the duplicates retained in the output file from file1. I tried a lot of combinations, but am not able to get to the final results due to the duplicates present in both the input files. Can someone please help me out here?

Re: Sorting on common fields with duplicates in the input files.

PostPosted: Wed Aug 19, 2009 1:21 am
by dick scherrer
Hello and welcome to the forum,

It will probably help if you post what you have coded so far and what happens when you run it.

Re: Sorting on common fields with duplicates in the input files.

PostPosted: Wed Aug 19, 2009 2:14 am
by lal
Hi Abhijat,
Following sort should give you the results as you are intending

//SORTJNF1 DD *                                               
LLLLLL AAAAAA 00000000000000000000000000                       
LLLLLL AAAAAA 11111111111111111111111111                       
MMMMMM AAAAAA 11111111111111111111111111                       
OOOOOO BBBBBB 00000000000000000000000000                       
PPPPPP BBBBBB 33333333333333333333333333                       
NNNNNN CCCCCC 00000000000000000000000000                       
/*                                                             
//SORTJNF2 DD *                                               
AAAAAA 11111111111111111111111111   DATA01                     
AAAAAA 11111111111111111111111111   DATA02                     
AAAAAA 11111111111111111111111111   DATA03                     
AAAAAA 22222222222222222222222222   DATA04                     
BBBBBB 00000000000000000000000000   DATA05                     
BBBBBB 00000000000000000000000000   DATA06                     
BBBBBB 11111111111111111111111111   DATA07                     
BBBBBB 22222222222222222222222222   DATA08                     
CCCCCC 00000000000000000000000000   DATA09                     
CCCCCC 11111111111111111111111111   DATA11             
CCCCCC 22222222222222222222222222   DATA12             
/*                                                     
//SORTOF01 DD SYSOUT=*                                 
//SYSIN    DD *                                         
   JOINKEYS FILE=F1,FIELDS=(8,6,A,15,26,A)             
   JOINKEYS FILE=F2,FIELDS=(1,6,A,8,26,A)               
   REFORMAT FIELDS=(F1:1,80)                           
   SORT FIELDS=(8,6,CH,A,15,26,CH,A,1,6,CH,A)           
   SUM FIELDS=NONE                                     
   OUTFIL FILES=01,BUILD=(1,80)                         
/*                                                     


Thanks,
Lal