>>Does anybody has knowledge about problems with joining tables with records that were deleted and after recalled in VFP 6.0 SP3
>>
>>The follwing select
>>
>>SELECT lojista.*, fatura.*, municip.nmmunicip, ;
>> produto.nmproduto, itemfat.qtproduto ;
>> FROM lojista, fatura, municip, itemfat, produto ;
>> WHERE fatura.nrfatura = itemfat.nrfatura AND ;
>> itemfat.nrproduto = produto.nrproduto AND ;
>> fatura.nrlojista = lojista.nrlojista AND ;
>> fatura.nrfatura >= thisform.txtnrfaturainicial.value AND ;
>> fatura.nrfatura <= thisform.txtnrfaturafinal.value AND ;
>> lojista.nrmunicipio = municip.nrmunicip ;
>> INTO CURSOR impfatura ;
>> ORDER BY fatura.nrfatura, produto.nmproduto
>>
>>works fine for some records but not for others.
>>
>>If I run the same command in VFP 5.0 it works ok, always.
>>
>>Is is VFP 6.0 bug?
>
>
>Sorry, but I think my problem is related with collating sequence, because I have changed the collate sequence to GENERAL. There are some bugs in MS knowledge base related with this.
>
>However, the weird is that all the fields used to make the comparison are numeric fields. How collate sequence can modify the results for SQL statments when the join condition uses just numeric fields?

Antonio Carlos,

I remember reading once a post from Christof Lange on this very subject. Check the following two PRG's. My conclusion: always use the MACHINE collation sequence, except for very special cases when you want to display character fields in your language order.


* Program...........: TEST_COLLATE1.PRG
* Author............: Christof Lange
* Project...........: Published on the UT 30/03/1999
* Created...........: 31/03/99 09:19:11
* Copyright.........:
* Compiler..........: Visual FoxPro 05.00.00.0415 for Windows
*) Description.......: For starters, here's a little sample program that shows extreme
*) : speed differences between COLLATE and MACHINE. Run the program once
*) : with GENERAL and once with MACHINE to see what I mean.
* Calling Samples...:
* Parameter List....:
* Major change list.:

LnBegTime = SECONDS()

*SET collate to "general"
SET collate to "machine"

SET talk off
SET Safety off
SYS(3054,11)

CREATE dbf dbf_a ( fielda1 N(2), fielda2 C(10) )
FOR ll = 1 to 1000
APPEND blank
ENDFOR
REPLACE all fielda1 with 99
REPLACE all fielda2 with "lol"
GOTO 300
REPLACE fielda2 with "find this!"
INDEX on fielda1 tag fielda1
INDEX on fielda2 tag fielda2
SELE 0
CREATE dbf dbf_b ( fieldb1 N(2), fieldb2 C(10) )
FOR ll = 1 to 1000
APPEND blank
ENDFOR
REPLACE all fieldb1 with 99
REPLACE all fieldb2 with "xlol"
GOTO 800
REPLACE fieldb2 with "find this!"
INDEX on fieldb1 tag fieldb1
INDEX on fieldb2 tag fieldb2
SET talk on
SELECT fieldb1, fieldb2 from dbf_a, dbf_b where fielda1 = fieldb1 and fielda2 = fieldb2 INTO CURSOR TEMP
CLOSE DATA ALL
lnEndTime = SECONDS()
WAIT WINDOW NOWAIT "Operation performed in: " + ALLTRIM( STR( lnEndTime - LnBegTime)) + " seconds"

*-- CHANGE - JCM - March 31, 1999 - 09:20:07
*-- Here's what I found on a PII 300 with 192M Ram:
*-- General: 27 secs
*-- Machine: 0 secs...
*-------------------------------------------------------------------
* Program...........: TEST_COLLATE2.PRG
* Author............: Christof Lange
* Project...........: Published on the UT 30/03/1999
* Created...........: 31/03/99 09:26:47
* Copyright.........:
* Compiler..........: Visual FoxPro 05.00.00.0415 for Windows
*) Description.......: And here's the next one. The following program should create
*) : an empty result set. It does so with SET COLLATE TO "MACHINE",
*) : but it returns a couple of records with SET COLLATE TO "GENERAL".
*) : It's a simple rule, never ever use GENERAL when you have numerical
*) : or integer fields. Even when there's no index involved
*) : (like in this example), the result set might be wrong.
* Calling Samples...:
* Parameter List....:
* Major change list.:


SET COLLATE TO "General"
*SET COLLATE TO "machine"

CREATE TABLE tmp (nID N(5))
USE tmp
FOR i = 1 TO 10000
APPEND BLANK
ENDFOR
REPLACE ALL nID WITH RECNO()

SELECT * FROM tmp INTO CURSOR Junk nofilter

SELECT * FROM tmp WHERE nID NOT IN (SELECT * FROM Junk)



José