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 --- _______________________________________________ Post Messages to: [email protected] 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] ** 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.
