Dynamic Table name in a query

Database
Enthusiast

Dynamic Table name in a query

Hello all,

 

I would like to join a table to my query in a procedure. But the table name needs to be dynamically generated based on a date column. (shown below) 

SyntaxEditor Code Snippet

sel * From table1 inner JOIN 'Tablename2_'||(LAST_DAY(date_column) (FORMAT 'YYYYMMDD') (VARCHAR(8))  ) as table2 on table1.a=table2.a

 How to accomplish this task? I understand this can be done in BTEQ but wanted to check if we can do it in Procedure.

 

Thank you,

Raghu

2 REPLIES
Teradata Employee

Re: Dynamic Table name in a query

In a stored procedure, you can use a cursor with dynamic SQL, something like

DECLARE SqlString VARCHAR(1024);
DECLARE DateString VARCHAR(8);
DECLARE DynCursor CURSOR FOR DynStmt;
... /* Obtain the value for DateString */
SET SqlString = 'SELECT * FROM Table1 JOIN Tablename2_'||DateString||' AS Table2 on Table1.a = Table2.a;'
PREPARE DynStmt FROM SqlString;
OPEN DynCursor;
Teradata Employee

Re: Dynamic Table name in a query

The SQL Stored Procedures manual shows an example of using Execute Immediate in the chapter, "Using Dynamic SQL in Stored Procedures."  In this case,

 

Declare dStmt VarChar(1024);
...
Set dStmt = 'select * From table1 inner JOIN Tablename2_' || (LAST_DAY(date_column) (FORMAT 'YYYYMMDD') (VARCHAR(8)) ) || ' as table2 on table1.a=table2.a';
Execute Immediate dStmt;