General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
My real world reason for using it is I have a numeric autoint field that I want to treat as a string. The field is named acctnumber. The index is
INDEX ON ALLT(STR(acctnumber)) TAG acctnumber.
I'm treating is as a string so I perform partial matches with ANSI OFF.
1255 = 12
Do you have a suggestion on another way to accomplish this?
Brian
>Without going into detail, I advice you to NEVER, I repeat NEVER, use ALLTRIM() in an index expression, it's asking for problems! All index expressions MUST give a fixed length value!
>
>>Would some try this sample code in VFP8, then again in VFP9? On my machine the both queries run optimized in VFP8, but only the 2nd one in VFP9. I don't know what settings to check other than ANSI and EXACT. Could it be a problem with VFP9?
>>
>>SET ANSI OFF
>>SET EXACT ON
>>CLEAR
>>* Turn rushmore reporting on
>>SYS(3054,2)
>>* Create a test cursor
>>CREATE CURSOR test (field C(10))
>>INDEX on field TAG field
>>INDEX on ALLTRIM(field) TAG field_t
>>* Put some data in the cursor
>>FOR i = 1 TO 10000
>> INSERT INTO test VALUES (SYS(2015))
>>ENDFOR
>>* Determine the last value
>>cValue = LEFT(test.field,LEN(ALLTRIM(test.field))-1)
>>* Try the untrimmed index
>>?"This one works"
>>?"---------------------------------"
>>SELECT * FROM test WHERE field = cValue INTO CURSOR index
>>* Try the trimmed index
>>?
>>?"This one does not work"
>>?"---------------------------------"
>>SELECT * FROM test WHERE ALLTRIM(field) = cValue INTO CURSOR noindex
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only