创建可变临时表Bank_Acct_AL1:
CREATE VOLATILE MULTISET TABLE Bank_Acct_AL1,NO LOG as
(select task_name
,count_num
,'' as c3
from dwPDATA.fanxiaoliang_unittest )WITH DATA
PRIMARY INDEX (task_name)
ON COMMIT PRESERVE ROWS;
查看创建好的表的定义:
show table Bank_Acct_AL1;
结果如下:
CREATE MULTISET VOLATILE TABLE DWJOB.Bank_Acct_AL1 ,NO FALLBACK ,
CHECKSUM = DEFAULT,
NO LOG
(
task_name VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,
count_num INTEGER,
c3 VARCHAR(0) CHARACTER SET UNICODE NOT CASESPECIFIC)
PRIMARY INDEX ( task_name )
ON COMMIT PRESERVE ROWS;
问题:
c3 字段为VARCHAR(0),如果我们向Bank_Acct_AL1表插入c3字段不为空字符的数据将会报错。
为了避免上述问题的出现,我们应该像下面那样处理,建表语句如下:
CREATE VOLATILE MULTISET TABLE Bank_Acct_AL1,NO LOG as
(select task_name