How to set a value to subquery select case variables

Question

How to pre-assign a value to row_num and accid in the below query in MySQL. Please help!!!!!!!!!!!

part of the stored procedure,

i had declare the variable and assigned the values as,

DECLARE row_num int;
DECLARE accid int;
SET row_num:=1;
SET accid:=1;

The above assigned values not set in the below query. it goes to else part and returning 1 to all rows.

INSERT INTO test1 (accountid,claum,opaum,valuationdate,rownumber)
SELECT accountid,
       claum,
       0,
       valuationdate,
       rownumber
FROM
  (SELECT accountid, 
          valuationdate, 
          claum, 
          @row_num:=(CASE WHEN @accid= accountid THEN @row_num+1 ELSE 1 END) AS rownumber, 
          @accid:=accountid AS acc
   FROM
     (SELECT accountid,
             valuationdate,
             SUM(aum) claum
      FROM tempaum
      GROUP BY accountid,
               valuationdate
      ORDER BY accountid,
               valuationdate) A
  ) claum;

Answer 1

You can set the values using a subquery. In addition, you should do all the variable assignments in a single statement. MySQL does not guarantee the order of evaluation of statements in a select statement. So:

INSERT INTO test1(accountid, claum, opaum, valuationdate, rownumber)
    SELECT accountid, claum, 0, valuationdate, rownumber
    FROM (SELECT accountid, valuationdate, claum, 
                 (@row_num := (CASE WHEN @accid = accountid THEN @row_num + 1
                                    WHEN @accid := accountid THEN 1
                                    ELSE 1
                               END) ) AS rownumber
          FROM (SELECT accountid, valuationdate, SUM(aum) as claum
                FROM tempaum
                GROUP BY accountid, valuationdate
               ) A CROSS JOIN
               (select @row_num := 0, @accid := NULL) params
          ORDER BY accountid, valuationdate
         ) claum;