Hi, Tamar,
Thanks for your interest. I can send you a PRG that creates 3 cursors and then does a query. I'll send it to you later today.
I've seen that there's more support for subqueries in Europa, but the biggest one that I haven't seen is support for scalar subqueries (using a SELECT SUM() as a column name).
Many times I've had one-to-many relationships involving multiple tables, such as a Job Master table and then a Job Labor Table with many records for each job, and also a Job Material table with many records for each job.
When I want to query to bring back a sum of labor hours and materials for each job, I can't do an outer join against both tables in 1 query, because I'll get double-counting.
The following query runs fine in SQL Server, but not in VFP:
SELECT JobMast.JobNumber, ;
(SELECT sum(JobLabor.LaborTime) FROM JobLabor WHERE JobLabor.JOBNUMBER
= JOBMAST.JOBNUMBER) AS LaborTime, ;
(SELECT SUM(material.amount) from material where material.jobnumber
= jobmast.jobnumber) as material ;
from jobmast group by JOBMAST.JOBNUMBER
I get 'invalid use of subquery' when I try it in VFP, becuase VFP does not support SELECT as a column. My alternatives are to build two queries, or use UDFs. I've usually opted for the former, but I wish I could accomplish things like this in one line of code.
So I'll send you a PRG. Thanks for your offer.
Kevin