Populate a table by looping through items in a list / another table
Quite often, when writing queries, we want to load a table with a condition we want to pass to it (rather than hard-coding the value in the WHERE clause). Eg, load sales for a given year.
This can be easily achieved by creating a Macro, and passing a value to it…
CREATE MACRO DBPROD_PLAY_PEN.Mac_LoadSalesLatestYear(vYearNumber INT) AS ( -- Begin Main Query INSERT INTO myTable SELECT YEAR_NUMBER, SUM(Scanned_Sales_Value) AS SalesValue FROM VWI0WKS_WKLY_ SALES wks JOIN VWI0TYW _YEAR_WEEK tyw ON tyw.YEAR_WEEK_NUMBER = wks.Year_Week_Number WHERE YEAR_NUMBER = :vYearNumber GROUP BY 1; -- End Main Query );
Then we can exec the macro, passing it the value of vYearNumber – the year I want to run data for
That’s fairly straight forward.
However, what if you had a list of year numbers held in a table, and you want to load each year into the sales table?
Of course, you’d just join to it, or add it into your filter. But depending on the size of the tables, and how they’ve been built, it may not always be possible to do it in one load – you need to load one year at a time, looping through the values.
This can be achieved by opening the table, assigning a cursor, and looping through the recordset running our query against each value.
CREATE PROCEDURE DBPROD_PLAY_PEN.Proc_LoopItemsInList() BEGIN DECLARE vYearNumber INTEGER; DECLARE vCountOfItems INTEGER; DECLARE Cursor1 CURSOR FOR SELECT Year_Number FROM DBPROD_PLAY_PEN.vtYearList FOR READ ONLY; SELECT COUNT(*) INTO vCountOfItems FROM DBPROD_PLAY_PEN.vtYearList; -- Count how many items we have to loop IF vCountOfItems <> 0 THEN --if you dont put this and the table is empty, you are in a never ending loop!!! OPEN Cursor1; Label1: LOOP FETCH Cursor1 INTO vYearNumber; --Assign the next value in the list to the variable vYearNumber -- Begin Main Query INSERT INTO DBPROD_PLAY_PEN.SalesByYear SELECT YEAR_NUMBER, SUM(Scanned_Sales_Value) AS SalesValue FROM VWI0WKS_WKLY_ SALES wks JOIN VWI0TYW _YEAR_WEEK tyw ON tyw.YEAR_WEEK_NUMBER = wks.Year_Week_Number WHERE YEAR_NUMBER = vYearNumber GROUP BY 1; -- End Main Query SELECT vCountOfItems-1 INTO vCountOfItems; --decrement the loop IF vCountOfItems = 0 THEN -- If 0, then you've cycled all the items in the table LEAVE Label1; END IF; END LOOP Label1; CLOSE Cursor1; END IF; END; CALL DBPROD_PLAY_PEN.Proc_LoopItemsInList();
What we’re doing here, is referencing a pre-populated table ‘vtYearList’, which is populated with 4 year values.
We first how many values are in the table, so we know how many times we must loop. It’s prudent to check this value doesn’t return zero, else you’ll end up stuck in a loop!
It’s then a matter of looping through the cursor set the relevant number of times, and running a query using the current assigned value. You’re effectively running the query 4 times with a different YEAR_NUMBER value.
This won’t always be the most efficient way of achieving the result, but has been useful for me in a few situations where the structure of the tables has favoured queries running for a single week/year.