Page 1 of 1

Join two files, replacing file1 values with file2 values

PostPosted: Thu Sep 20, 2012 8:12 pm
by BGronli
I have two files, both LRECL=455, both with keys 1,32,A,81,39,A. Where the keys match, I need to replace the values from file2 33,38,CH in file1, same starting spot and length. I used the following code as a basis,
//STEP0100 EXEC PGM=SORT                               
//SYSOUT   DD SYSOUT=*                                 
//INA      DD DISP=SHR,DSN=Your Input FB 600 Byte file
//INB      DD DISP=SHR,DSN=Your Input FB 80 Byte file
//SORTOUT  DD SYSOUT=*                                 
//SYSIN    DD *                                         
  OPTION COPY                                           
  JOINKEYS F1=INA,FIELDS=(06,9,A)                       
  JOINKEYS F2=INB,FIELDS=(10,9,A)                       
  JOIN UNPAIRED,F1                                     
  REFORMAT FIELDS=(F1:1,600,?,F2:1,9)                   
  INREC IFOUTLEN=600,                                   
  IFTHEN=(WHEN=(601,1,CH,EQ,C'B'),OVERLAY=(20:602,9))   
//*

to get to
//STEP0100 EXEC PGM=SORT                                         
//SYSOUT   DD SYSOUT=A                                           
//INA      DD DISP=SHR,DSN=D.WIT.WRK.ALMTBALS                   
//INB      DD DISP=SHR,DSN=D.WIT.WRK.ALMT.BALS                   
//SORTOUT  DD SYSOUT=A                                           
//SYSIN    DD *                                                 
  OPTION COPY                                                   
  JOINKEYS F1=INA,FIELDS=(1,32,A,81,39,A)                       
  JOINKEYS F2=INB,FIELDS=(1,32,A,81,39,A)                       
  JOIN UNPAIRED,F1                                               
  REFORMAT FIELDS=(F1:1,455,?,F2:33,48)                         
  INREC IFOUTLEN=455,                                           
  IFTHEN=(WHEN=(456,1,CH,EQ,C'B'),OVERLAY=(33:457,48))           
/*

In the IFTHEN, why should it be looking for a B in position 456? Also, do I have this setup correctly to replace values from file2 in file1 when the keys match and to keep unpaired records in file1?

Thank you,
Brad

Re: Join two files, replacing file1 values with file2 values

PostPosted: Thu Sep 20, 2012 9:51 pm
by skolusu
BGronli wrote:I have two files, both LRECL=455, both with keys 1,32,A,81,39,A. Where the keys match, I need to replace the values from file2 33,38,CH in file1, same starting spot and length. I used the following code as a basis,
In the IFTHEN, why should it be looking for a B in position 456? Also, do I have this setup correctly to replace values from file2 in file1 when the keys match and to keep unpaired records in file1?
Thank you,
Brad


BGronli,

Your syntax cards are correct. As for your question as to why you need to validate the indicator at position 456, you used JOIN UNPAIRED,F1 which writes out unpaired records from F1 as well as paired records. For paired records you can replace the contents from file 2 but for unpaired records , DFSORT does not know what to replace , so it fills with the default FILL character which is spaces thus by overlaying the original content. In order to preserve the original content we add an indicator at the end and also the file2 contents in a temp location (usually at the end) and then we validate the indicator to overlay file 2 values on to file1.

? on the REFORMAT statement indicates a 1-byte indicator is to be included in each joined record. The indicator will be set to one of the following values in each paired or unpaired joined record, as appropriate:

'B' - the key was found in F1 and F2.

'1' - the key was found in F1, but not in F2.

'2' - the key was found in F2, but not in F1.

So B denotes a matching record and we are only overlaying the contents of file2 using IFTHEN statement.

Check this link for a detailed explanation of JOINKEYS

http://publibz.boulder.ibm.com/cgi-bin/ ... E1CA60/4.0

Re: Join two files, replacing file1 values with file2 values

PostPosted: Thu Mar 07, 2013 2:22 am
by BGronli
skolusu,

Thank you for your earlier response and I apologize for not returning sooner to do so. With regards to your example, should the output file be larger than the file being updated? When I run the following:
//SORT4    EXEC PGM=SORT                                 
//SYSOUT   DD SYSOUT=A                                   
//SORTJNF1 DD DSN=D.WIT.WRK.ALMTBALS,DISP=SHR             
//SORTJNF2 DD DSN=D.WIT.WRK.ALMT.BALS,DISP=SHR           
//SORTOUT  DD DSN=D.WIT.WRK.ALMT.BALS2,DISP=(NEW,CATLG), 
//            DCB=(RECFM=FB,BLKSIZE=0)                   
//SYSIN    DD *                                           
  OPTION COPY                                             
  JOINKEYS F1=SORTJNF1,FIELDS=(1,32,A,81,11,A)           
  JOINKEYS F2=SORTJNF2,FIELDS=(1,32,A,81,11,A)           
  JOIN UNPAIRED,F1                                       
  REFORMAT FIELDS=(F1:1,455,?,F2:33,48)                   
  INREC IFOUTLEN=455,                                     
  IFTHEN=(WHEN=(456,1,CH,EQ,C'B'),OVERLAY=(33:457,48))   
/*                                                       
SORTJNF1 has 823,433 records. SORTJNF2 has 54,627 records. I would think that SORTOUT should also have 823,433 records, however, it has 1,089,719. Any ideas?

Re: Join two files, replacing file1 values with file2 values

PostPosted: Thu Mar 07, 2013 2:29 am
by skolusu
BGronli wrote:skolusu,

SORTJNF1 has 823,433 records. SORTJNF2 has 54,627 records. I would think that SORTOUT should also have 823,433 records, however, it has 1,089,719. Any ideas?


I am guessing that you have duplicates on the keys you matching in both files. For ex: if you have 3 duplicates in file1 for a key and file2 has 5 duplicates for the same key, the resulting output will be 3 X 5 = 15 rows.

So Check if you have duplicates and if you don't want a cartesian product , then you can eliminate duplicates using JNF1 or JNF2. Let me know if that is what you want and I will show you a way to do it.

Re: Join two files, replacing file1 values with file2 values

PostPosted: Thu Mar 07, 2013 3:11 am
by BGronli
I did find duplicates and the problem was in the
  JOINKEYS F1=SORTJNF1,FIELDS=(1,32,A,81,11,A)           
  JOINKEYS F2=SORTJNF2,FIELDS=(1,32,A,81,11,A)           
statements. They both should have been (1,32,A,81,39,A). Once I corrected that and ran, I have the correct number in the SORTOUT, 823,433.

Thanks again for the help.

Re: Join two files, replacing file1 values with file2 values

PostPosted: Mon Jul 01, 2019 10:02 pm
by Oreod123
I had a similar question in another post and had used the above sort/join suggestion.. I wanted to thank users Skolusu and BGronli for the great help... Also I'm not sure how to thank them and that is why I was posting in the thread!

Re: Join two files, replacing file1 values with file2 values

PostPosted: Tue Jul 02, 2019 2:36 pm
by NicC
There is a "thumbs up" icon on the right of the row of icons above a post.

but...Greod123 has not been seen in years and Kolusu, who is a DFSort developer, does not participate in these forums but has his own.