Steve,

How about flipping the relation around

select callrecords
index on dialedno tag dialedno

select rates
index on dialcode tag dialcode descending
**(the DESC should put the longer numbers first)

set relation to alltrim(dialcode) into callrecords
set skip to callrecords

replace callrecords.rate with rate, callrecords.charge with rate*callrecord.duration FOR empty(callrecord.rate)

This should hit the longest dialcode first and skip callrecords which have already found a match.



>I'm rating phone call records based on 2 tables: call records, and rates.
>
>Here's some data for example
>
>Callrecords.dbf
>DialedNo Duration Rate Charge
>0115526960000 5mins ?? ??
>0115089999999 6mins ?? ??
>
>Rates.dbf
>DialCode RatePerMin
>01155 .10
>0115 .05
>
>Currently I index the rates table on the the length of dial code and on dial code(i.e. len(dialcode)+dialcode ). Then I scan call records for a match (starting with the longest dial code), replace the rate and charge with values calculated from the rates table, and temporarily delete the call record (so it can't match a shorter dial code). Then jump to the next rate, scan the call records again etc. for however many rates I have (often 2000 of them) with big call record files (sometimes 1/4 million records) this can take forever.
>
>Anyone have any ideas to speed this up? Is there some way I could relate these tables on the longest match of DialedNo to DialCode? (If so then I could do all my replacements on a single pass through the CallRecords table)
>
>Thanks so much for any ideas,
>Steve