>very cool stuff... I even modified the code to be even more random and could not make it crash the way I intended. Problem is, my SQL is EXACTLY the same as what we are playing with in our examples and it still gives me the same garbage. I even modified the field types to be exactly the same as mine; DOUBLEs and DATETIMEs.
>
>I have rebuilt the table on my c: drive without any connection to a DBC or any indexes. I have appended the original data into the file. Execute the SQL statement with an order by on the Patient ID, so that I can see 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. What I actually see is 2, 4, 5, 7, 9, and 10.
>
>
>Try this... :-)
>
>* pcode added for extra fields to validate to
>CREATE TABLE c:\patadd (patient_id B(0), patadd_id B(0), ef_date T, pcode C(10))
>
>*Copy the following to a text file: c:\output.txt
>
>1,1,04/01/1997 00:20:00,"T0G0P0"
>1,2,05/20/2000 00:02:00,"T0A2W0"
>2,3,01/22/2002 07:51:00,"T7P1R6"
>3,4,01/14/2002 08:06:00,"T7P2P6"
>4,5,01/22/2000 20:25:00,"T7P2P4"
>5,6,01/05/1999 09:13:00,"T7P1Y6"
>5,7,10/22/2001 09:00:00,"T7P1Y2"
>6,8,01/24/2002 07:43:00,"T7P1J6"
>7,9,04/01/1997 08:45:00,"T7P2P5"
>7,10,05/20/1997 23:14:00,"T7P2P5"
>8,11,07/29/2001 10:32:00,"T7P2E2"
>9,12,12/27/1999 10:06:00,"T0G0Z0"
>10,13,09/29/2000 17:35:00,"T7P1E5"
>
>select patadd
>append from c:\output delimited
>
>
>* This is the exact SQL statement (same as yours)
>
>select * from c:\patadd t1 ;
> where t1.ef_date == (select max(t2.ef_date) from c:\patadd t2 ;
> where t1.patient_id = t2.patient_id) ;
> order by t1.patient_id
>
>
>Enjoy! I didn't :-)
Gordon,
There datetime field comparison caught you. Never use datetime in comparisons like this or you would get unexpected results.
Try this :
Select * From patadd t1 ;
where Ttoc(t1.ef_date,1) == ;
(Select Max(Ttoc(t2.ef_date,1)) From patadd t2 ;
where t1.patient_id = t2.patient_id) ;
order By t1.patient_id
Cetin
