I'm looking for a method to execute a Stored Procedure where I can Loop through Month/Year values each loop. I have:
I can get an Update statement to populate one month at a time, but I'd like to change the month each Update Loop & add the Amount for the relevant Row_ID & MonYr.
I am only going to try this for a small number of records as I've been advised of the impact of Looping in SPs.
Can anyone assist here?
Here's my attempt at an UPDATE statement with a Macro in a Loop;
I'm not sure how to get the MonYr to change each Loop.
Replace PROCEDURE dbc.SP_LOOP_TEST ( IN i INTEGER, IN j INTEGER) BEGIN DECLARE ii integer; set ii=i; while (ii<j) do begin set ii=ii+1; UPDATE dbc.AA_ROWID_MONYR_TRNSPSD (Has a Column ("A") of Row IDs and MonYr Values (200701-202312) from "B1" across Row 1. FROM AA_TRANSPOSE_TEST A (Contains 3 Columns - 1.Matching Row_IDs from the above table, 2. MonYr Values (200701-202312), & 3. an Amount value per Row INNER JOIN dbc.DATE_DIM B (Contains just MonYr values (200701-202312) ON B.Clndr_Month_Id = A.MonYr LEFT JOIN dbc.AA_ROWID_MONYR_TRNSPSD C ON A.Clndr_Month_Id = C.Variable_MonYr -- This (C.Variable_MonYr) value should change each Loop from 200701 to 200702 ... etc SET C.Variable_MonYr = A.Val_Pl WHERE A.Row_ID = C.Row_ID AND B.MonYr = C.Variable_MonYr -- This (C.Variable_MonYr) value should change each Loop from 200701 to 200702 ... etc ; end; end while; END;
I've got a working version for one UPDATE below, I'd welcome any suggestions on how to Loop through this UPDATE changing the MonYr value each time.
BEGIN DECLARE ii integer; set ii=i; while (ii<j) do begin set ii=ii+1; UPDATE dbc.AA_ROWID_MONYR_TRNSPSD (Column 'A' has Row_IDs and the Range 'B1 - ZZ1' has MonYr values in this format (_200701, _200702 ... ) as they are Column Field Names FROM ( SELECT A.Row_ID, A.MonYr, A.Val_Pl FROM dbc.AA_TRANSPOSE_TEST A (Contains 3 Columns, 1. Row_IDs matching the above table, 2. MonYr values, 3. An Amount field) LEFT JOIN dbc.AA_ROWID_MONYR_TRNSPSD B ON A.Row_ID = B.Row_ID ) AS S SET _200701 = S.Val_Pl -- I need to change '_200701' to '_200702' etc each loop as this will change to the next Column. WHERE Row_ID = S.Row_ID AND 200701 = S.MonYr -- I also need to change '200701' to '200702' etc here each loop as this is the Amount to enter into the correct Row_ID & MonYr ; end; end while; END;