Looping a macro over a list using Cursors


Looping a macro over a list using Cursors

Hi all,

Due to low spool space, my existing macro, that summarizes and inserts sales data per week (taking a week parameter) will need to be complemented by a store parameter to get smaller chunks of data. I will have to execute it per week and store to get the full result set and not use excessive spool.

I have been trying to add a Cursor to iterate through the data set, adding a store_id to the where clause of the macro but fail, due to bad syntax; For some reason I dont get the samples posted to excecute in my environment



Could someone please post a super easy sample of a cursor that is executable in TD Studio (14.10) and iterates through a list of values, that I could extend to fit my macro? Maybe depend on dbc tables?


Thanks in advance - working code matters! 


Re: Looping a macro over a list using Cursors

I would expect this would work but I am getting 'parse error splitting statement' issues


BEGIN Transaction;

DECLARE v_StoreID integer;

FOR loop_Store AS crs_Store CURSOR FOR SELECT distinct Store_ID from myDB.Store_Dimension;


SET :v_StoreID = loop_Store.Store_ID;

SELECT :v_StoreID;


END Transaction;