>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
Well, this type of SQL is extremely difficult to understand as you concatenated everything one line after the other. In VFP, you are allowed to execute very long line before being forced to do such.
Basically, without trying to provide you the exact answer, I would first suggest you chop that in two SQLs. Have a first one to extract the keys only. Basically, such a SQL will only retrieve the keys + the required fields of the GROUP BY. Add to that the INNER JOIN, WHERE clause and such. No order is needed as this will be covered in your second SQL. Then, do another SQL to inner join with your first SQL cursor to get the rest of fields and add your order.