Information générale
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
Versions des environnements
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
Précédent
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement