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

EXEC  DBPROD_PLAY_PEN.Mac_LoadSalesLatestYear(2014)

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.