In this case you have to create a view Create view viewname (EID,GrossEarn) as Select pr.EID, Sum(pr.GROSSPAY) from PRHIST pr, ESOP eh Where pr.EID = eh.EID group by pr.EID
Then use it to do a correlated update. Put the view first so it will work fast UPDATE ESOP SET GROSSEARN = T1.GrossEarn from viewname t1, ESOP T2 where t1. eid = t2.eid Dennis McGrath ________________________________ From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Gray, Damon Sent: Friday, November 21, 2008 3:14 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Why is this an illegal select function? 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]<mailto:[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 ( ) ( ) ----------\ (----) /----------- \__) (__/
