>If SQL Server is ANSI SQL and VFP 8 is, how come the following two lines can't be run as is:
>
>
>SQLExec(lnHandle,"SELECT YEAR(AddDate) AS Test FROM News GROUP BY YEAR(AddDate)")
>SELECT YEAR(AddDate) AS Test FROM News GROUP BY YEAR(AddDate)
>
>
>VFP doesn't support GROUP BY on expression but SQL Server does. So, does that mean VFP 8 supports only some of ANSI SQL or is it that this is specific to SQL Server and doesn't apply to ANSI SQL?
Michel,
SQL server is ANSISQL-92 compliant with some extensions for efficiency. OTOH VFP SQL is not fully ANSISQL-92 compliant.
There are many commands (or clauses should I say) that makes SQL usage much easier in SQL server. VFP doesn't have them - but adds some of them. ie: Insert/Select existed in SQL server but introduced to VFP in VFP8.
As per group by not only that above syntax is valid there are others like CUBE and ROLLUP. These exist in ANSISQL-92 AFAIK but not in VFP implementation.
IOW these are theoretic differences where I might be saying wrong too (though read SQL server architecture at least 2 times my memory might not serve well:) I decided not to care about their differences much and find a working solution in both as need arises :)
SELECT YEAR(AddDate) AS Test FROM News GROUP BY Test
or
SELECT YEAR(AddDate) AS Test FROM News GROUP BY 1
Would work in VFP.
Cetin