>Without the indexes select will work. Without the indexes sum while will only work if the data is sorted. Without the indexes, seek cannot be done.
>
>The following demonstrates that having the index open will allow Rushmore to use it regardless of SET ORDER. That is the way it's always been. The structural CDX is open, but SET ORDER is not required for Rushmore optimization. In fact, it's irrelevant because VFP SQL reopens the cursors itself.
>
>
>CLEAR
>*Create table no .idx files.
>
>LOCAL lcSetSafety
>lcSetSafety = SET("SAFETY")
>
>SET safety off
>CREATE TABLE NAOMI FREE (cField1 c(10))
>
>LOCAL m.lnI
>FOR m.lnI = 1 TO 5
> INSERT INTO NAOMI (cField1) VALUES (TRANSFORM(m.lnI))
>ENDFOR
>
>*No indexes
>SYS(3054,1)
>SELECT * FROM NAOMI WHERE UPPER(cField1) = '3' INTO CURSOR c_Temp
>SYS(3054,0)
>
>*Index .idx
>
>SELECT naomi
>INDEX ON UPPER(cField1) TO cfield1.idx
>
>SET ORDER TO 0
>
>*.IDX NO SET ORDER
>SYS(3054,1)
>SELECT * FROM NAOMI WHERE UPPER(cField1) = '3' INTO CURSOR c_Temp
>SYS(3054,0)
>
>SELECT naomi
>
>SET INDEX TO cfield1.idx
>*.IDX NO SET ORDER
>SYS(3054,1)
>SELECT * FROM NAOMI WHERE UPPER(cField1) = '3' INTO CURSOR c_Temp
>SYS(3054,0)
>
>SET SAFETY &lcSetSafety.
>
>Try the query without the indexes and without set order. That should give you the correct result. Then see about adding the .idx via SET INDEX not via SET ORDER
Thanks, I'll try to run my query with set order to, but I don't really think it's the root of the problem. I try anyway.
If it's not broken, fix it until it is.
My Blog