Hi - I have a very large chunk of code that accumulates data from relational tables over the past 25 months worth of data. I wrote the code to test against 6 months data which works fine. When I run it against 25 I get a message saying "run out of spool space".
While there may well be inefficiencies in my code, it does work and I don't have time to rewrite and get the output I need.
What I was hoping to do is to use macro's to parameterize the code such that I could run 1 months worth of data at once and then join the tables together at the end.
The code goes something like this:
create table myDB.report_data1 as (
from myDB.BigTable a
left join... (select....
on a.member_id = b.member_id left join... (select....
on a.member_id = c.member_id etc.etc
I have a feeling that it isn't poss to "create table" in TD macro's... is this correct?
If so, does anyone have any ideas as to how I can go about running code for all 25 months?
You can do a Create Table as long as it is the last statement in the macro. Not really sure why you want to do it as a macro though - why not just create the 25 tables (one for each month) in a bteq script.
If you are thinking of parameterising the macro call with a tablename - forget it. The macro is parsed at macro creation time, so you could pass in a parameter of the month, but you cannot parameterise the Report_Data1, 2, ...
You could parameterise these in Unix/ Linux, calling a standard bteq script 25 times with date and tablenames as parameters for shell substitution.
Alternatively, create the 25 tables once and delete/ insert them in a macro.
Do not try to populate all 25 tables in a single macro call - that runs as a single transaction and you will probably run out of spool again.