To get your query optimized, you need
SET COLLATE TO "General"
>Hi all,
>
>Been a long time since I dug into "General" indexes so want to check something out before I tinker with a customer's setup:
>
>- I changed their lastname/firstname indexes a few years ago to "General" from "machine" so that they would sort correctly with accented characters
>- I therefore also dropped wrapping the index expression in UPPER() since general indexes are case-insensitive (or that's what I thought)
>
>e.g. old INDEX ON UPPER(Lastname) TAG Lastname
>became INDEX ON Lastname TAG Lastname
>
>- I therefore changed some of their other queries to drop using UPPER(); just a few days ago a user called to say that they did not get a match when searching a client file. I double-checked and it worked for me - until I realized that it had failed because of a difference in case ("DeJong" instead of "Dejong"). I thought my queries were case insensitive because the query matched an index
>
>e.g. SELECT...WHERE Lastname = "DeJong" matches index "INDEX ON Lastname"
>
>So if I change the query back to:
>
>WHERE UPPER(Lastname) = "DEJONG"
>
>then to get query optimization, I should be changing the tag back to UPPER(Lastname)
>
>Two questions:
>
>1) am I correct in assumptions and should I proceed with changing back the tags to include UPPER()
>2) how will inserting UPPER() into a tag that is GENERAL affect sort order (it doesn't seem to but I need to be sure).
>
>Thanks,
>
>Albert Gostick
Thierry Nivelet
FoxinCloud
Give your VFP application a second life, web-based, in YOUR cloud
http://foxincloud.com/Never explain, never complain (Queen Elizabeth II)