>>>
>>>dsID BETWEEN(dsID , ?lnIdLow, ?lnIDHigh) ;
>>>AND state+extryear+extrweek between(state+extryear+extrweek, ?lcStWkLow, ?lcStWkHIgh)
>>>
>>>
>>>Crazy, huh?
>>
>>Keith,
>>
>>Originally I had this SQL with BETWEEN as a command. I changed it myself to use BETWEEN as function (view designer creates COMMAND like SQL, I prefer to use FUNCTION type). In both cases, it doesn't make a difference. I guess, I just would not trust this show plan info. Obviously all three indexes DELETED(), StateWeek, DsID must be used. I don't see a reason, why they don't.
>
>I think you missed my point. It seems VFP needs to see the Index expression as the first part of each component of the WHERE clause. It's like a mix of SQL BETWEEN and function BETWEEN(). Give it a try with DsID immediately before the BETWEEN() function and inside the parentheses.
I think, Help is a little bit misleading here. Anyway, I've tried in command window:
sys(3054,11)
SELECT rcode, town, record_fm, record_to, landct_fm, landct_to, ;
calmnth, mdflag, nodata, notes, ddflag, dsid, state, extryear, extrweek, ;
ccode FROM support!filingdate ;
WHERE BETWEEN(dsID , 0, 999999) ;
AND between(state+extryear+extrweek, "MA200001", "MA200201") ;
ORDER BY extryear DESC, extrweek DESC, rcode, town
And it shows full optimization. All three indexes (including deleted - though I probably get rid of it) are used. I guess, EView show plan sometimes doesn't show this info correctly.
If it's not broken, fix it until it is.
My Blog