>Hi friends
>am just wondering why followin syntax does not work ?? am trying last result date and last result value of each patient.
>
>SELECT
>master.NHI as Patient_Numbers_UR_Number,;
>master.name as Name_Address_Full_Known_Name,;
>(SELECT MAX(rp.resdate) FROM res_pat as rp WHERE ;
> rp.master = rp1.master) as Last_result_date,;
>(select rp4.value from res_pat as rp4 where rp4.master=rp1.master ;
>and rp4.resdate in (SELECT MAX(rp.resdate) FROM res_pat as rp WHERE rp.master = rp1.master)) as [Column4]
>
>from master inner join res_pat as rp1 on master.pkey = rp1.master;
>where rp1.pkey in ;
>(select res_pat1.pkey FROM master,;
>test_pat test_pat1, test test2, res_pat res_pat1;
>WHERE ;
>(UPPER (PADR(test2.Name,30)) LIKE "BMI");
>AND year (res_pat1.Resdate) = 2005 AND;
>(master.status = 0 AND test_pat1.MASTER = master.PKEY AND test_pat1.status < 2;
> AND test_pat1.TEST = test2.PKEY AND test2.status < 2;
> AND res_pat1.TEST_PAT = test_pat1.PKEY AND res_pat1.status < 2);
>);
>group by 1,2,3,4
>
>it works perfectly in my sql server database but not in foxpro. btw abv command works nicely in foxpro too if i remove "column4".
>any ideas ?

vfp9 support 1 level only in correlating field.

CREATE CURSOR test (ii i)

SELECT * FROM test

SELECT (SELECT MAX(II) FROM TEST) FROM TEST

SELECT (SELECT MAX(II) FROM TEST WHERE II=XX.II) FROM TEST XX

SELECT (SELECT MAX(II) FROM TEST WHERE II=(SELECT MAX(II) FROM TEST)) FROM TEST XX

* this fail
SELECT (SELECT MAX(II) FROM TEST WHERE II=(SELECT MAX(II) FROM TEST WHERE II=XX.II)) FROM TEST XX

projection subquery are slow in VFP.

SELECT DISTINCT;
master.NHI as Patient_Numbers_UR_Number,;
master.name as Name_Address_Full_Known_Name,;
Last_result_date,;
rp1.value AS [Column4];
from master;
 join (select master,MAX(rp.resdate) Last_result_date from res_pat group by master) last;
  on last.master = rp1.master;
  inner join res_pat as rp1 ;
  on rp1.master  = last.master AND rp1.resdate=last.Last_result_date ;
where rp1.pkey in ; && simplified this part: put the IN into the inner join
 (select res_pat1.pkey FROM master,;
 test_pat test_pat1, test test2, res_pat res_pat1; && put join here !!!!!
 WHERE ;
  (UPPER (PADR(test2.Name,30)) LIKE "BMI");
   AND year (res_pat1.Resdate) = 2005	AND;
  (master.status = 0 AND test_pat1.MASTER = master.PKEY AND test_pat1.status < 2;
	AND test_pat1.TEST = test2.PKEY AND test2.status < 2;
	AND res_pat1.TEST_PAT = test_pat1.PKEY AND res_pat1.status < 2);
);

you can simplified it more a lot ( remove the distinct clause ).