Set enginebeharior 70 Add that setting before the query, if you want it to behave like it did in 6.
JH -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ajoy Khaund Sent: Sunday, July 27, 2008 12:53 AM To: Profox Subject: Vfp6 to Vfp9 Sql Group By Hi All, I need some advice (maybe not a great day to ask as its weekend for many but since I am at it) on a query which has a group by. I am getting some fields from the Product master and the stock qty from productlot table. Vfp6 Select a.prodname,a.iunit,a.avgcost as unitcost,sum(b.balunits),a.productid,a.categoryid,a.convfactor; from Products a, Productlot b; where a.productid = b.productid; and b.balunits > 0; group by 5; order by 1 into cursor View1 Vfp9 Select a.prodname,a.iunit,a.avgcost as unitcost,(Select sum(b.balunits) from Productlot where productid = a.productid) as balunits,a.productid,a.categoryid,a.convfactor; from Products a; order by 1 into cursor View1 Readwrite Select View1 Delete for balunits = 0 *I did not want to make the query very complicated so I decided to include zero values in the query and then delete them. (Any Comments ????) OR Select productid,sum(balunits) as balunits; from Productlot; where balunits > 0; group by 1; order by 1 into cursor View1a Select a.prodname,a.iunit,a.avgcost as unitcost,b.balunits,a.productid,a.categoryid,a.convfactor; from Products a, View1a b; where a.productid = b.productid; order by 1 into cursor View1 Both will solve the purpose. Is there any better way to do this. In TIA Ajoy Khaund Neamati Road Near Bhogdoi Bridge Jorhat 785 001 Assam, India Tel: 91-376-2351288 Cell: 91-94350-92287 Mail: [EMAIL PROTECTED] Mail: [EMAIL PROTECTED] "Walking on water and developing software from a specification are easy if both are frozen." - Edward V. Berard, "Life-Cycle Approaches" --- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html --- [excessive quoting removed by server] _______________________________________________ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED]@shelbynet.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
