>Thx for the speedy reply. I was wanting to convert my sql statements to the new standard. An example would be below. What would i have to do to convert to the following statment?? I have tried used group by 5,1 and i was still getting the error message "Group By clause is missing or invalid"
>
>Thx again
>
>llsql = "select salesdetail.cprodclass, "
>llsql = llsql + "productclass.cprdclsdes, 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 "
>
>
>&llsql into cursor xzrptcrs
You have to include every field in the Select list that is not being aggregated. e.g., all fields not being SUMmed.
GROUP BY superclass.cclassid, salesdetail.cprodclass, productclass.cprdclsdes, productclass.csupercls, Productclass.linclinroy, superclass.cdesc, superclass.lprintxz
The order of the fields in the GROUP BY will also matter with respect to the results set.
Mark McCasland
Midlothian, TX USA