sql - teradata 中的动态选择表

Question

我是 teradata 的新手，我正在尝试在 teradata 中创建动态表。我已经创建了这段代码，但是有很多错误。帮助表示赞赏！

当前我手动编码：

select a_num,id from tablename union
select a_num,id from tablename_1912 union
select a_num,id from tablename_2001

注：1912、2001应该是动态的

预期的：

select a_num,id from tablename union 
select a_num,id from tablename_1912(dynamically change) union    
select a_num,id from tablename_2001(dynamically change)

我的代码：

create procedure test_sp()
Create volatile table test(Account Number varchar(50),Id varchar(30) on commit preserve rows;

Begin 

Declare SQLL varchar(max);
Declare Schema_name_1 varchar(20);
Declare Schema_name_2 varchar(20);
Declare table_name varchar(30);
Declare underscore varhcar(10),
Declare 3_months_previous varchar(50);
Declare 2_months_previous varchar(50);

set schema_name_1 = 'Some_Schema_name1';
set schema_name_2 = 'Some_Schema_name2';
set table_name = 'test';
set 3_months_previous = substr(add_months(current_date,-3),3,2)||substr(add_months(current_date,-3),6,2);
set 2_months_previous = substr(add_months(current_date,-2),3,2)||substr(add_months(current_date,-2),6,2);
set underscore  = '_';
set SQLL = 'select A_Num,''11''+ from '+Schema_name_1+table_name+ ' union all '+
           'select B_Num,''22''+ from '+Schema_name_2+table_name+ ' union all '+
           'select A_Num,''23''+ from '+Schema_name_1+table_name+underscore+3_months_previous ' union all '+
           'select B_Num,''24''+ from '+Schema_name_2+table_name+underscore+2_months_previous ;

End;

insert into test
call test_sp();

select * from test t join another_Table c on t.a_num=c.p_num

标签： sqloracleteradatateradata-sql-assistant