UPDATE cAgent ;
SET yMonth = dtSum.yMonth ;
FROM cAgent ;
JOIN ;
(SELECT SUM(yAmount) AS yMonth, ;
iAgentID, MONTH(dDate) AS nMonth, ;
Year(dDate) AS nYear ;
FROM cMovs ;
GROUP BY iAgentID, nMonth, nYear) dtSum ;
ON dtSum.iAgentID = cAgent.iID ;
AND dtSum.nMonth = cAgent.nMonth ;
AND dtSum.nYear = cAgent.nYear>Lets supose I have a table with every entry in the system:
>>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.".
>
>How should I create that sentece?, I'd like to avoid the use of SCAN ... ENDSCAN to get an literal cmovs.iAgentID = ScannedTable.IID.
>
>Is it possible?, or is this a of ANSI-SQL-rule issue?. I could think that that's possible!
>
>Update:The title should day "SubQuery" not Correlated
>
>Thanks in advance
