>>Hi!
>>
>>I'm running VFP6 (SP5) on Czech Windows 98 SE and have the following problem
>>in SQL-SELECT with comparing character data from these tables:
>>- two tables in the FoxPro 2.6 (DOS) format, code page 895 (Kamenicky)
>> with structure (Klic Character 1, Value Numeric 3) and 256 records
>> with values from CHR(0),0 to CHR(255),255. These tables are identical.
>> They have no indexes.
>>
>>After running the following code
>>
>> SET COLLATE TO "MACHINE"
>> USE i:\fox\keys IN 0 SHARED
>> USE i:\fox\keys2 IN 0 SHARED
>>
>> sele * FROM KEYS K1, KEYS2 K2 ;
>> WHERE K1.Klic == K2.Klic ;
>> ORDER BY 2
>>
>>I get only 245 records in the result with statements that 176=95, 177=95,
>>record for code 172 is missing etc. I expected 256 records with identical
>>values :-((
>
>We had a similar discussion in near past with SQL string comparison unexpected results. Maybe this would easier in your case :
>
>

sele * FROM KEYS K1 ;
> inner join KEYS2 K2 ;
> on asc(k1.Klic) = asc(k2.klic) ;
> ORDER BY 2

Hi,

I've find a final solution for my problem (please, keep in mind
I need to compare strings longer than 1 character. 1 character
field Klic is in my examples only for a demonstration - so the
solution with ASC(..) comparison is OK for this example, but 
not for my needs).

Here is the source:

SET COLLATE TO "MACHINE"
USE i:\fox\keys IN 0 SHARED
USE i:\fox\keys2 IN 0 SHARED

SELECT Keys
SET NOCPTRANS TO Klic
SELECT Keys2
SET NOCPTRANS TO Klic

SELECT * FROM KEYS  INTO CURSOR CK1
SELECT * FROM KEYS2 INTO CURSOR CK2

SELECT * FROM CK1 K1, CK2 K2	;
	WHERE K1.Klic = K2.Klic	;
	ORDER BY 2

* other house-keeping here - close cursors etc.

As I understand it:
It seems to me, that VFP is not able to solve code-page translation
and strings comparison in one-shot. So, the first two SELECTs do
the code-page translation (from page 895 to page 1250), except
the Klic fields (SET NOCPTRANS is a must here, SET COLLATE TO "MACHINE"
doesn't work here as it does in FoxPro 2.6 for DOS :-O ). The final
SELECT compares these two cursors, and as they are in Windows
code page (1250), the SET COLLATE TO "MACHINE" works OK and the
result is right.
And this solution is far far faster than that one with calling UDF
to compare the strings.
Any other ideas ?

Pavel Kouril

Pavel Kouril