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 > > ( ) ( ) > > ----------\ (----) /----------- > > \__) (__/ > > > > >
