Moving to 9!! Help with sql-select 1

[Judi201]

Hi!

I'm finally doing it. I am moving my app to 9.

I understood that I had some queries that would have to be rewritten because they were done at the very first (before I knew better).

I know that GROUP BY must include all selected fields and I know that you experts have many ways to accomplish this.

So I am hoping someone will help me rewrite the following code so that I can understand the possibilities.

SELECT  Scptypes.scptype, Scptypes.scpdesc, Sum(Scrap.scpamnt);
		FROM Casting!Scrap ;
		LEFT JOIN Casting!Scptypes ON VAL(Scrap.scptype) = VAL(Scptypes.scptype) ;
		WHERE Scrap.jobsnumb = lcJobsnumb AND Scrap.ordrnumb = lcOrdrnumb ;
		ORDER BY Scptypes.scptype ;
		GROUP BY Scptypes.scptype ;
		INTO CURSOR csroScrap

Thanks

Judi

 

[bborissov]

SELECT  Scptypes.scptype, Scptypes.scpdesc,;
        Sum(Scrap.scpamnt) AS scpamnt;
FROM Casting!Scrap ;
LEFT JOIN Casting!Scptypes ON Scrap.scptype = Scptypes.scptype ;
WHERE Scrap.jobsnumb = lcJobsnumb AND;
      Scrap.ordrnumb = lcOrdrnumb ;
GROUP BY Scptypes.scptype, Scptypes.scpdesc;
ORDER BY Scptypes.scptype ;
INTO CURSOR csroScrap

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[Judi201]

Borislav,

Thanks for your response. That takes care of that but I should have posted a better example for that didn't help me much with the following.

SELECT Orders.jobsnumb, Orders.partnumb, Orders.ordrnumb,;
	Orders.ordrdate, Orders.custno AS ocustno, Orders.purdate, Orders.purnumb,;
	Orders.purwght, Orders.pursetu, Orders.purrate,;
	Orders.revdate, Orders.duedate, Orders.duetotl, ;
	Orders.shpboxs, Orders.shpaddw,;
	Orders.shpdamt, Orders.shpwght, Orders.returns,;
	Orders.retsave, Orders.scrappc, Customer.custno AS bcustno, Customer.bname,;
	Customer.battn, Customer.baddress, Customer.bcity, Customer.bst,;
	Customer.bzip, Customer.bcountry, Customer.finance, Customer.terms,;
	Customer.salesman, Customer.shipvia, Customer.fob, Shipaddr.custno AS scustno,;
	Shipaddr.saddno, Shipaddr.sname, Shipaddr.sattn, Shipaddr.saddress,;
	Shipaddr.scity, Shipaddr.sst, Shipaddr.scountry, Shipaddr.szip, ;
	Orders.sAddno, Orders.shpdate, Orders.shpcomp,;
	Orders.shipped, Orders.shptotl, Orders.purtotl, ;
	SUM(Orders.purwght * Orders.shipped) AS totwght, ;
	SUM(Orders.purrate * Orders.shipped) AS totcost, ;
	SUM(IIF((Orders.shptotl <= Orders.purtotl) , ;
	(Orders.purtotl - Orders.shptotl + Orders.shipped), ;
	Orders.shipped -(shptotl - purtotl)))AS ordrreq ;
	FROM  casting!Orders LEFT OUTER JOIN casting!Customer ;
	LEFT OUTER JOIN casting!Shipaddr ;
	ON  Customer.custno = Shipaddr.custno ;
	ON  Orders.custno = Customer.custno ;
	WHERE Orders.shpdate BETWEEN lcBegin AND lcEnd  ;
	GROUP BY  Orders.custno,Orders.purnumb, Orders.ordrnumb ;
	ORDER BY  Orders.custno,Orders.purnumb, Orders.jobsnumb ;
	INTO CURSOR  Temp1
*** now get backordered amt
SELECT *, ;
	SUM(IIF(Orders.shptotl < Orders.purtotl, ;
	ordrreq - shipped, 00000)) AS shipbo ;
	FROM  Temp1 ;
	GROUP BY  bcustno, purnumb, ordrnumb ;
	ORDER BY  bcustno, purnumb, ordrnumb ;
	INTO CURSOR csrInvoice

Would someone please show me how to do this to meet 9's requirements.

Thanks

Judi

 

[bborissov]

You have 3 choices:
1. Put all non agregate fields in GROUP BY:

GROUP BY  Orders.custno,Orders.purnumb, Orders.ordrnumb, Orders.jobsnumb ....

2. Make all field you didn't care in GROUP to be Agregate. Use MAX(Fields) AS Field.

SELECT MAX(Orders.jobsnumb) AS jobsnumb, Orders.partnumb, Orders.ordrnumb,;
.....

3. Use SET ENGINEBEHAVIOUR 70 (NOT RECOMENDED)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[Judi201]

Borislav,

Thanks. That makes it clear. The "MAX(Orders.jobsnumb) AS jobsnumb," is the part I needed. I have seen various such functions used and didn't know which ones I could use for any field without changing the meaning. And in something this conplicated I am bound to make a mistake somewhere.

Your answer is clear and direct and I should be able to go from here.

Appreciate your help as always.

Judi

 

[bborissov]

Glad to help

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.