>>We have found a discrepancy in the results from SQL Selects in VFP7, depending on whether the files being queried have indexes or not. Try running the following code and see for yourself. I am using VFP7 with the 1st service pack (VFP version: 07.00.0000.9262).
>>
>>We also tried this with VFP6 SP5 and found even different results. (see comments in following code).
>>
>>The purpose of these select statements is to locate records that exist in the "left" table that are not in the "right" table.
>
>There's a documented change in the behavior of queries with outer joins involving nulls. I think it's in the Readme file.
>
>Tamar
Hi Tamar,
It doesn't seem to apply in this case. As Mark pointed out it behaves different if indexed or not (in VFP7 itself).
It seems that ISNULL() is the evil there. I found working solution both in VFP6 and VFP7 and it caused more confusion on me :)
where udf(Isnull(tbl2.userid))
function udf
lparameters tuValue
return tuValue
makes it work in VFP7 but not in VFP6.
where nvl(tbl2.userid,'') == ''
makes it work in both.
where nvl(tbl2.userid,0) == ''
works in VFP6 and doesn't error datatype mismatch ! (Latter was a trial for different datatypes)
This sounds to be similar to :
set deleted on
delete all from mytable
select max(dDate) from mytable && returns as if no deletions with d,t types
bug in VFP5 (Pascal Declerq draw attention yesterday).
Cetin