I understand the VFP compliance with SQL standards, and in general agree with that compliance. However, that has created a catch 22 problem, which if I have been informed correctly, the standard, seems to be a purist dream, but a real world nightmare.
SQL standards require, that when an aggregate field is included in a SQL Statement, a Group by clause is mandatory, and all non-aggregate fields must be included in the Group By clause. Memo fields can't be included in a Group By clause, but the Engine requires ALL fields, including any Memo fields to be included. Ergo, a VFP8 compliant view cannot be created to drive a form. IOW:
Rule #1: A memo field can't be included in a Group By clause.
Rule #2: All non-aggregate fields must be included in the Group By clause. When VFP8 objects, refer to Rule #1.
It has been suggested to me that aggregate fields should never be included in an editable view. I consider that to be just plain silly, but even if that point were to be conceded (I won't), a SQL cursor couldn't even be created to drive a report. Yeah, I know the report writer can do some aggregation, but the data is often cleaner (and may well be faster) when created with a SQL statement. But the report engine isn't the issue here. ANSI SQL syntax is.
Would some of the VFP gurus care to comment on this issue, and perhaps suggest solutions (other than trimming a memo field into a character field which is not an option) ???
Steve Buttress, MCP
ProMatrix MVP - Life
ProSysPlus Developer