>CREATE CURSOR cMovs (iid int autoinc, iAgentID int, yAmount y, dDate d) > >RAND(-1) >FOR lnCounter=1 TO 100 > INSERT INTO cMovs (iAgentID, yAmount, dDate) ; > VALUES ((RAND()*10)-1, RAND()*1000000,date(2005,01,01)+ (RAND()*45)) >ENDFOR >>And I have an entity where I would like to get the sumary of each agent by month and year...
>CREATE CURSOR cAgent (iid int autoinc, yMonth y, nMonth int, nYear int) >FOR lnCounter=1 TO 10 > INSERT INTO cAgent (yMonth, nMonth, nYear) ; > VALUES (0,1,2005) >ENDFOR >>I'd like to Update the cAgent cursor with a single UPDATE-SQL...
>UPDATE cAgent ; > SET yMonth = (SELECT SUM(yAmount) ; > FROM cMovs ; > WHERE cMovs.iAgentID = cAgent.iID > AND MONTH(dDate)= cAgent.nMonth ; > AND YEAR(dDate) = cAgent.nYear) ; > WHERE cAgent.nMonth = 1 AND cAgent.Year=2005 >>If I do the above UPDATE-SQL I get the Error 1828 "SQL: Illegal GROUP BY in subquery.".
Select iAgentId, Month(dDate) As Month, Year(dDate) As Year, Sum(yAmount) As YTot ; FROM cMovs ; GROUP By 1,2,3 ; into Cursor crsSums ; nofilter Update cAgent ; SET yMonth = t2.YTot ; FROM crsSums t2 ; WHERE t2.iAgentId = cAgent.iid ; AND cAgent.nMonth = 1 And cAgent.nYear=2005 ; And t2.Month = cAgent.nMonth ; AND t2.Year = cAgent.nYearCetin