Thanks once again to all for the great education I'm getting!
_____ From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Bill Downall Sent: Friday, November 21, 2008 1:48 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Why is this an illegal select function? Damon, Two steps needed to do what you want: CREATE TEMP VIEW SumPay (EID, SumGrossPay) AS SELECT EID, SUM (GrossPay) FROM PrHist GROUP BY EID UPDATE ESOP SET GrossEarn = SumGrossPay FROM ESOP ep, SumPay s2 WHERE s2.EID = ep.EID Bill UPDATE ESOP SET GROSSEARN = SUM (ph.GROSSPAY) FROM PRHIST ph, ESOP ep WHERE ph.EID = ep.EID GROUP BY ph.EID On Fri, Nov 21, 2008 at 4:14 PM, Gray, Damon <[EMAIL PROTECTED]> wrote: Bill, I understand your logic. Does this also apply when I'm trying to do an UPDATE SET? UPDATE ESOP SET GROSSEARN = SUM (ph.GROSSPAY) FROM PRHIST ph, ESOP ep WHERE ph.EID = ep.EID GROUP BY ph.EID I've tried several permutations of this with no joy. I get no error message, but am just told that no rows satisfy the specified clause. I know that in excess of 12,000 rows should be involved. _____ From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Bill Downall Sent: Friday, November 21, 2008 10:54 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Why is this an illegal select function? Damon, EID defines a "grouping", and SUM defines an aggregate calculation for each distinct value in the grouping. When you combine a column like EID with an aggregate calculation like SUM, you must also have a GROUP BY in your select. And it will always be the list of all the columns that are not within aggregate expressions. Select pr.EID, Sum(pr.GROSSPAY) From PRHIST pr, ESOP eh Where pr.EID = eh.EID Group by pr.EID Bill On Fri, Nov 21, 2008 at 1:50 PM, Gray, Damon <[EMAIL PROTECTED]> wrote: Select pr.EID, Sum(pr.GROSSPAY) From PRHIST pr, ESOP eh Where pr.EID = eh.EID wwwww ( @ @ ) -------oOO---(_)---OOo------- Damon J. Gray Anvil Corporation Business Services (360) 937-0770 oooo0 0oooo ( ) ( ) ----------\ (----) /----------- \__) (__/
