I'm not very experienced with the Load and Unload Tools, I just got a simple BTEQ Script to export a single resultset to work. Is there a way to split the export by a row value, how can I do that? What I want is a separate flat export file for each value in my result set, e.g. 10 files for 10 customer id's (customer_1.csv, customer_2.csv...) and don't want to do that manually. Please give me a kick in the right direction :-)
You can split your export data by using a variable in WHERE condition. e.g. you can put this select statement in a macro and then calll the macro, and pass the value as paramter for where clause.
To reduce database load and save time my intention is to execute the statement only once and split the resultset export dynamically based on a defined row value after the resultset is received. Is it possible to do something like this on teradata level, or would I have to that afterwards with an external script that splits the export? In my understanding in the example with 10 customer id's I would have to call and execute a parameterized macro ten times manually, or did I miss something?
It is no one time operation, the query will be executed regularly with different and much more then the ten customers in my example.
I think then it would be good for you to use stored procedure, write a stored procedure to loop through a range you want to process, pass parameters of date or CustomerID.
Stored procedure give you the best control over repeating statements.
I still don't see how to avoid multiple query execution. Do I have to use a cursor and loop through the resultset, is it even possible to export data to a file within a stored procedure? I did some research and can't find examples how to do that. Or would I have to insert the filtered resultsets in a multiple volatile tables that will be exported after sp execution within a bteq script?