VFP 8.0 and Group By 1

SET ENGINEBEHAVIOR 70 before the statement
SET ENGINEBEHAVIOR 80 after the statement.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

Is there a way to make the abouve statment work without the set enginebehavior?

Redsz

Yes, technically all fields involved in the group by clause have to mentionned in the select statement.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

How about: Of course you can change the order of these fields if it makes a difference: OR you can use MIN() or MAX() on these fields in the field list, and leave them out of the GROUP BY list.

Rick

To clarify Mike G's answer, the field list in the select clause and the group by have to be EXACTLY THE SAME, although the order of the fields does not matter. This applies to all fields that are not involved in aggregate functions. A simple example should illustrate. The statement:

select a,b,c,sum(d) from table1 group by a,b,c

is correct. You can't leave out any of the fields of a,b, or c in either clause unless you leave them out of both clauses.

In prior versions of VFP, it apparently was not required that the field lists match, although I have been coding SQL for 20 years and VFP for 5 years and I never knew that.

It has always been standard SQL structure that all non-aggretated fields in the SELECT clause be included in the GROUP BY clause.

I would change the ENGINEBEHAVIOR only as a last resort. It's better to write the SQL correctly in the first place.

In your example, you have 6 non-aggregated fields in your select clause and only 2 in your group by clause. There has to be the same number of fields in each:

llsql = "select salesdetail.cprodclass, "
llsql = llsql + "productclass.cprdclsdes,"
llsql = llsql + "productclass.csupercls, "
llsql = llsql + "Productclass.linclinroy, "
llsql = llsql + "superclass.cdesc, superclass.lprintxz, "

llsql = llsql + "sum(salesdetail.ntotdisc) as sumdisc, "
llsql = llsql + "sum(salesdetail.ntotretail) as sumret, "
llsql = llsql + "sum(salesdetail.nqty) as sumqty "
llsql = llsql + "from pos!salesdetail "
llsql = llsql + "left outer join pos!productclass "
llsql = llsql + "on salesdetail.cprodclass = "
llsql = llsql + "productclass.cprodclass "
llsql = llsql + "Left outer join pos!superclass "
llsql = llsql + "on alltrim(productclass.csupercls) = "
llsql = llsql + "superclass.cclassid "
llsql = llsql + "where " + pcand
llsql = llsql + " and productclass.crecstatus = 'A' "

llsql = llsql + "Group by "
llsql = llsql + "salesdetail.cprodclass, "
llsql = llsql + "productclass.cprdclsdes,"
llsql = llsql + "productclass.csupercls, "
llsql = llsql + "Productclass.linclinroy, "
llsql = llsql + "superclass.cdesc, superclass.lprintxz, "

llsql = llsql + "order by superclass.cclassid, "
llsql = llsql + "salesdetail.cprodclass "





Mike Krausnick

mkrausnick

Thanks Mike, I just didn't have the energy to write all that.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.