>SQL Server likes this query, but FoxPro doesn't. Foxpro gives
>
>Function name is missing ).
>
>Anybody know how to reword it to work?
>
>select rtrim(clients.fname)+rtrim(' '+clients.mname)+' '+rtrim(clients.lname) as fullname, (select top 1 plans.effective from plans where plans.clientno=clients.clientno and plans.effective>date() order by plans.effective) as meetdate, (select top 1 plans.meet_time from plans where plans.clientno=clients.clientno and plans.effective>date() order by plans.effective) as meettime from clients into table formlet2 order by clients.lname, clients.fname, clients.mname, clients.clientno
Randy,
This would work with SQL server, however are you sure it's correct ? To me it sounds like this would fail if target date had more than one meettime ?
Apart from that below might be sample to formulate VFP counterpart :
create cursor myGrouper (Ino i, ddate d, irec i)
for ix = 1 to 20
insert into myGrouper ;
values (int(rand()*5), date()-int(rand()*100), ix)
endfor
select * from myGrouper order by 1,2
select * from myGrouper A where ddate = ;
(select max(ddate) from myGrouper B where a.ino = b.ino) ;
order by ino
What it does is to pull out last date rows per ino (dates are enetered in random order).
Cetin