Hi

I have troubles with a SQL select. I will not give me consistent results.
The correct result in the sample would be the 2 records marked with (&&*).
But if I run the sample (10 loops) I get about 40% false answers. It either returns the correct 2 or 0 records.
I checked it in VFP6 and VFP7. What am I missing here?

Thanks for any help.


Here is some sample code:
-------------------------------------
clear
clear all

for i = 1 to 10

&& Get random results here
cTimeStamp = datetime()

create Cursor vo (surveykey i, surveydate t, ref_nr i, regisstat i)

insert into vo values (10001, cTimeStamp , 1, 0)
insert into vo values (10002, cTimeStamp+100, 1, 0) &&*
insert into vo values (10003, cTimeStamp-100, 1, 0)

insert into vo values (20001, cTimeStamp , 2, 0)
insert into vo values (20002, cTimeStamp+100, 2,99)
insert into vo values (20003, cTimeStamp-100, 2, 0)

insert into vo values (30001, cTimeStamp-100, 3, 0)
insert into vo values (30002, cTimeStamp+ 0 , 3, 0)
insert into vo values (30003, cTimeStamp+100, 3, 0) &&*

cFilter = ".t."

SELECT vo.SurveyKey, vo.surveydate, vo.ref_nr;
FROM vo INTO cursor sel_temp2 WHERE &cFilter

select MAX(surveydate) as surveydate, ref_nr;
FROM sel_temp2 group by ref_nr into cursor sel_temp3

&& This is the problematic select
SELECT vo.surveykey FROM vo, sel_temp3 ;
WHERE vo.ref_nr = sel_temp3.ref_nr AND;
vo.surveydate = sel_temp3.surveydate and vo.regisstat # 99 ;
INTO CURSOR validKeys

? cTimeStamp, _tally

use in sel_temp2
use in sel_temp3
use in vo
wait window timeout 1

next