|
|
|
Forum Newbie
Group: Forum Members
Last Login: 9/4/2008 9:57:05 AM
Posts: 2,
Visits: 30
|
|
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?
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Yesterday @ 7:30:07 AM
Posts: 197,
Visits: 611
|
|
Hello,
You can use Dynamic SQL in Stored-Procedure for this purpose. You may dump the results in some temporary tables, within dynamic-sql statement.
Regards,
Adeel
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 9/4/2008 9:57:05 AM
Posts: 2,
Visits: 30
|
|
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)
BATCH_SK: DECIMAL(18)
DATE_UPDATED: TIMESTAMP
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.
|
|
|
|