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) ) as ( echo '.set separator " | " '; echo '.set rtitle "Invalid Code" '; echo '.set format on'; select * from :TABLE_NM WHERE :DSCRP_CLMN_NM = 'INVALID CODE'; );
So those two parameters will be read from the rows of another table and use them as parameters. How can I do that?
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:
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.