< It's difficult to say without knowing what you would like changed. However, there are some changes coming to the SQL in VFP 9.0.
Craig,
I forgot to answer this question...
A good example is scalar subqueries. 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 time records for each job and a Job Material table with many materials records for each job.
When I want to query to bring back a sum of 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.
Kevin