>

SELECT sku, stockcolor, SUM(quantity);
>  FROM Products;
>  GROUP BY sku;
>  INTO CURSOR resultset
>

>
>George,
>
>In the above select the stockcolor has an undefined value as any one sku may have many colors or not. Becuase the stockcolor is not in the group by the sql interpreter cannot guarantee that the value of stockcolor will be a valid or exepcted value. Valid SQL would either drop the stockcolor field from the query or include it in the group by clause.
>
>Just about every other SQL product will disallow that syntax. Try that query in SQL Server's Query analyzer, and there you have no option to change the behavior. We have been spoiled because VFP has allowed invlaid sql in the past and now in VFP8 we can turn on a checker that will disallow the invalid SQL statements.

JimB,

I’m well aware of the how’s and why’s of this situation. What I was trying to do was give a simple example of something that, I think, isn’t easily put into words. Namely that when aggregate functions are involved you can’t have a field not using one in the select statement that isn’t included in the group by clause (how’s that? < g >).

Taking my own work as an example, I have one system that has well over 20 different group by clauses: shift, machine, style, shop order, operator, team, gauge group, etc., and combinations of these. Since the fields that are not involved in the group by clause aren’t displayed, whether or not they contain valid values isn’t relevant. In either VFP 8.0 or SQL Server 7.0 and above, you have several possible solutions.

The most obvious is to create an SQL statement or stored procedure for each. This will produce the best performance. However, the downside, to this approach, of course, is that you have 20+ code modules or stored procedures to maintain. I don’t think that anyone would want to do such a thing if not absolutely necessary.

The next approach would be to concatenate a string and execute it, either via macro expansion, or execution via SPT or a stored procedure. This eliminates the need for 20+ modules or SPs. Unfortunately, this performs the worst and, IMV, is more difficult to maintain.

Finally, we have the ability, in both SQL Server and VFP, to set the compatibility level to circumvent this problem. By using sp_dbcmptlevel we can set SQL Server’s compatibility to 6.5. This, because SQL Server is multi-threaded and it’s applied to an entire database, isn’t something you’d want to do since it may negatively impact other SPs. However, if the applicable data set is returned to VFP, using SET ENGINEBEHAVIOR 70 (then, of course, restoring it to 80) can accomplish the same thing. This eliminates multiple trips between the client and the server, and gives us a single module to maintain. Certainly, the performance isn’t going to be as good as calling a single stored procedure or module, but it should be better than the previous approach.

Comments?

Regards,