Unstable code 1

[lifesupport]

The replace below sometimes works and sometimes does not. I wanted to use a 'replace all' as opposed to a scan loop because a 'replace all' is faster. Currently I have a scan loop and replace each record individually and it's reliable, but my client says it's too slow. What makes the 'replace all' unstable? Thanks

Select tmpratefile
Index ON dircode TAG dirprod
Set ORDER TO dirprod
Go top

Select rankfile
Set ORDER TO
Go top
Set relation TO dircode INTO tmpratefile

Set Multilocks On
CursorSetProp('buffering',3,'rankfile')
Replace ALL rankfile.RATE With tmpratefile.RATE FOR ((tmpratefile.dircode+tmpratefile.prodcode)=(rankfile.dircode+rankfile.APRODUCT))
Tableupdate(0,.T.,'rankfile')
Flush

 

[mm0000]

Change the code to:

Select tmpratefile
Index ON dircode+prodcode TAG dirprod
Set ORDER TO dirprod

Select rankfile
Set ORDER TO
Set relation TO dircode+aproduct INTO tmpratefile

Set Multilocks On
CursorSetProp('buffering',3,'rankfile')
Replace ALL rankfile.RATE With tmpratefile.RATE FOR ((tmpratefile.dircode+tmpratefile.prodcode)=(rankfile.dircode+rankfile.APRODUCT))
Tableupdate(0,.T.,'rankfile')
Flush

 

[mmerlinn]

[ ]
I just ran into a similar problem in FP2x.

I don't know if this applies in VFP or not, but in FP2x REPLACE ALL works perfectly as long as NONE of the replaced fields has an index tied to it.

If I REPLACE ALL on an indexed field, it works fine UNTIL the replaced record is moved past one or more possible records to replace. At that point, one or more records are not updated.

After thinking about it, I think the problem lies in that the index is probably updated after every replace. And when that happens the record pointer is unchanged, but at a different place in the indexed table. If this place is BEFORE all remaining records yet to be replaced, everything works fine. However, if it is moved past one or more that have not yet been replaced, the replace command cannot find those skipped records.

In FP2x, my solution was to remove indicies from the table with records needing replacing, then restore the indicies after the replacements. Now everything works fine.




mmerlinn

"Political correctness is the BADGE of a COWARD!"

 

[lifesupport]

Thanks for the input. I tried the suggested code, but it didn't work. There is no index on the rate field which is the only field being replaced. It seems that although the relationship has been established, the program is not able to read the tmpratefile table even though there is a matching record in that table.

 

[lifesupport]

Got it!
mm0000 got me going into the right direction. I kept the field prodcode in tmpratefile, but added a column called aproduct which has the same exact info as prodcode because the matching column in rankfile is called column. Apparently it works better if the related fields have the same name. The difference in name was the major problem. These are the hoops I had to jump thru to get it working:

Select tmpratefile
Index ON dircode+prodcode TAG dirprod
Index ON dircode+aproduct TAG aproduct additive
Set ORDER TO aproduct
Go top

Select rankfile
Set MULTILOCKS ON
CursorSetProp('buffering',1,'rankfile')
Set ORDER TO
GO top
Set relation TO (rankfile.dircode+rankfile.aproduct) INTO tmpratefile IN rankfile
GO top

Set Multilocks On
CursorSetProp('buffering',5,'rankfile')
Replace ALL rankfile.RATE With tmpratefile.RATE FOR ((tmpratefile.dircode+tmpratefile.aproduct)=(rankfile.dircode+rankfile.APRODUCT))
Tableupdate(0,.T.,'rankfile')
Flush