General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
The DateTime data type is a floating point value. That's probably the reason for your getting results that appear incorrect.
Jay
>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
Previous
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only