Hi Lutz,

SET COLLATE TO ... affects string comparation for operator "="

SET COLLATE TO "GENERAL"
?"GALA"= "Gala"
?"A"= "a"
SET COLLATE TO "MACHINE" && binary
?"GALA"= "Gala"
?"A"= "a"


MartinaJ

>Hi Martina,
>
>SET COLLATE should do nothing an a simple comparison?
>I see no INDEX ON.
>So you say this is some internal SQL indexing behaving oddly?
>
>I have SET COLLATE TO "MACHINE" as default, sure, but never thought about this.
>
>Lutz
>
>>Hi,
>>
>>Add next row to your code:
>>

>>SET COLLATE TO "MACHINE"
>>

>>
>>For other collates can be "GALA"= "Gala"
>>
>>MartinaJ
>>
>>>*Cursor with unique list of string
>>>CREATE CURSOR c_UniqueString (cStr C(50))
>>>
>>>*Cursor where we want to find all unique string from.
>>>CREATE CURSOR c_Data (cCode C(40), cdesc C(50))
>>>
>>>INSERT INTO c_data (ccode, cDesc) VALUES ("ARIA", "Ariane")
>>>INSERT INTO c_data (ccode, cDesc) VALUES ("GALA", "Gala")
>>>
>>>SET EXACT ON
>>>SET ANSI ON
>>>
>>>*First column
>>>SELECT DISTINCT CAST(RTRIM(cCode) AS V(40)) as string FROM c_data WHERE !ISNULL(cCode) INTO CURSOR c_DistinctCode
>>>
>>>INSERT INTO c_UniqueString (cStr) SELECT string FROM c_DistinctCode WHERE string NOT IN (SELECT cStr FROM c_UniqueString)
>>>
>>>*Second column
>>>SELECT DISTINCT CAST(RTRIM(cdesc) AS V(40)) as string FROM c_data WHERE !ISNULL(cdesc) INTO CURSOR c_DistinctDesc
>>>
>>>INSERT INTO c_UniqueString (cStr) SELECT string FROM c_DistinctDesc WHERE string NOT IN (SELECT cStr FROM c_UniqueString)
>>>
>>>*We should have 4 values in c_UniqueString, but we are missing Gala in lower case. The NOT IN does not take into account Lowercase and UpperCase.
>>>
>>>Any work around ?