>It looks like this was supposed to be fixed in SP1, "Indexes on STR() are penalized during Rushmore optimization if SET ANSI is OFF". Unfortunately, we're still seeing the same problem. We've got a 400MB table that has this index:
>

>STR(VAL(CUST_NUM), 8) + SYS(11, TRANS_DATE)
>

>Under VFP 8, this query is optimized:
>

>SELECT * ;
>  FROM custhist;
> WHERE STR(VAL(cust_num), 8)+SYS(11, TRANS_DATE) = "633621"
>

>Under VFP 9, even with the SP1, we don't get any optimization. Under VFP 8 we do.
>
>It's easy enough to reproduce, try these in both VFP 8 & VFP 9:
>

>SYS(3054, 11)
>CREATE TABLE c:\testopt (cust_num C(8), trans_date D)
>INDEX ON STR(VAL(CUST_NUM),8)+SYS(11,TRANS_DATE)  TAG custhist
>SELECT * ;
>  FROM testopt;
> WHERE STR(VAL(cust_num),8)+SYS(11,TRANS_DATE) = "  633621"
>

Not tested, but what about this index expression as a workaround:

INDEX ON PADL(TRANSFORM(CUST_NUM),8)+SYS(11,TRANS_DATE)  TAG custhist