VFP 8.0 and Group By 1

[Redsz]

I know this has been posted before and i have read these posts and im still confused as to how to properly use the clause. I know i can set the enginebehaviour as a workaround but i was hoping somebody could explaing how to properly use this clause.

SQL statment i was using in vfp 6.0. Cant get this freaking thing to work in 8.0.

Help much appreciated.

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 superclass.cclassid, "
llsql = llsql + "salesdetail.cprodclass "
llsql = llsql + "order by superclass.cclassid, "
llsql = llsql + "salesdetail.cprodclass "

 

[Mike Gagnon]

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.

 

[Redsz]

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

 

[Mike Gagnon]

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.

 

[rgbean]

How about:

TEXT TO llsql TEXTMERGE NOSHOW
select salesdetail.cprodclass, 
  productclass.cprdclsdes,
  productclass.csupercls, 
  Productclass.linclinroy,
  superclass.cdesc, superclass.lprintxz,
  sum(salesdetail.ntotdisc) as sumdisc,
  sum(salesdetail.ntotretail) as sumret,
  sum(salesdetail.nqty) as sumqty 
 from pos!salesdetail
  Left outer join pos!productclass 
    on salesdetail.cprodclass = productclass.cprodclass
  Left outer join pos!superclass 
    on alltrim(productclass.csupercls) = superclass.cclassid
 where <<pcand>> and productclass.crecstatus = 'A'
 Group by superclass.cclassid, salesdetail.cprodclass,
  productclass.cprdclsdes, productclass.csupercls,
  Productclass.linclinroy, 
  superclass.cdesc, superclass.lprintxz
 Order by superclass.cclassid, salesdetail.cprodclass
ENDTEXT

Of course you can change the order of these fields if it makes a difference:

  productclass.cprdclsdes, productclass.csupercls,
  Productclass.linclinroy, 
  superclass.cdesc, superclass.lprintxz

OR you can use MIN() or MAX() on these fields in the field list, and leave them out of the GROUP BY list.

Rick

 

[mkrausnick]

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

 

[Mike Gagnon]

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.