How to read parameters from a table?


How to read parameters from a table?

Is it possible to read say a list of table names from a table and then use them as parameters in a macro or bteq? I have a master table list that contains the all the names of the tables that I have to read and export data from.

CREATE MACRO displayinvalidcode (TABLE_NM varchar(30), DSCRP_CLMN_NM varchar(80) )
echo '.set separator " | " ';
echo '.set rtitle "Invalid Code" ';
echo '.set format on';
select * from :TABLE_NM

So those two parameters will be read from the rows of another table and use them as parameters. How can I do that?
Teradata Employee

Re: How to read parameters from a table?


You can use Dynamic SQL in Stored-Procedure for this purpose. You may dump the results in some temporary tables, within dynamic-sql statement.



Re: How to read parameters from a table?

Sorry, could you explain a little more? So I should have a stored procedure which uses dynamic sql to create a temporary table with the same structure as the tables I am looking at(All the tables I'm looking at have the same 4 column definitions), and then it populates these tables and then exports the data? The tables I am looking at have this structure:

Table_code: CHAR(5)
Description: VARCHAR (100)

I want to export the data from the tables that have a row where Description = 'INVALID', and create seperate flat files for each one. But I don't want to create a flat file if the table does not have any 'INVALID' rows for the Description.

So, the stored procedure would create a temporary table with the 'INVALID CODE' rows, than I would use bteq to export the data for the temp table to a flat file? Sorry I am not so familiar with stored procedures and dynamic SQL for teradata.