代码之家 › 专栏 › 技术社区 › Mimi Müller

使用bteq插入Teradata时的时间戳无效

create-table sql-insert teradata sql

Mimi Müller · 技术社区 · 7 年前

所以我想在一个以前用bteq创建的表中插入一些内容。我已经在谷歌上搜索过了 here )但是我已经在列上进行了强制转换,这并没有消除错误。

创建语句如下所示:

.logmech LDAP
.logon databae/user_id,pwd
.set width 256
.set retcancel on database libname;
DATABASE libname;
create MULTISET table libname.IRIS( "SEPAL_LENGTH" FLOAT,  "SEPAL_WIDTH" FLOAT,  "PETAL_LENGTH" FLOAT,  "PETAL_WIDTH" FLOAT,  "TARGET" FLOAT,  "TIMESTAMP1" TIMESTAMP(6) FORMAT 'YYYY-MM-DDBHH:MI:SS.S(6)' DEFAULT NULL ,  "NULL_VALUES" FLOAT) UNIQUE PRIMARY INDEX("TimeStamp1");
.QUIT
.LOGOFF
.EXIT

插入语句如下:

.logmech LDAP
.logon databae/user_id,pwd
.set width 256
.set retcancel on database libname;
DATABASE libname;
.IMPORT VARTEXT FILE =/path_to_file/data.csv, skip=1;
.REPEAT *
USING "SEPAL_LENGTH" (varchar(2000)),  "SEPAL_WIDTH" (varchar(2000)),  "PETAL_LENGTH" (varchar(2000)),  "PETAL_WIDTH" (varchar(2000)),  "TARGET" (varchar(2000)),  "TIMESTAMP1" (varchar(2000)),  "NULL_VALUES" (varchar(2000)) 
INSERT INTO IRIS_delete VALUES(cast( :"SEPAL_LENGTH"  as FLOAT), cast( :"SEPAL_WIDTH"  as FLOAT), cast( :"PETAL_LENGTH"  as FLOAT), cast( :"PETAL_WIDTH"  as FLOAT), cast( :"TARGET"  as FLOAT), cast( :"TIMESTAMP1"  as TIMESTAMP(6) FORMAT 'YYYY-MM-DDBHH:MI:SS.S(6)'), cast( :"NULL_VALUES"  as FLOAT));
.QUIT
.LOGOFF
.EXIT

通过执行insert语句,我得到了错误:

***失败6760无效时间戳。语句1,信息=0

以下是csv的一些示例:

| sepal_length_sepal_width_patal_length_patal_width_target_timestamp1空_值 0 5.1 3.5 1.4 0.2 0.0 2018-09-18 13:26:30.583216|

1 4.9 3.0 1.4 0.2 0.0 2018-09-18 13:26:30.583216|

2 4.7 3.2 1.3 0.2 0.0 2018-09-18 13:26:30.583216|

3 4.6 3.1 1.5 0.2 0.0 2018-09-18 13:26:30.583216|

4 5.0 3.6 1.4 0.2 0.0 2018-09-18 13:26:30.583216|

5 5.4 3.9 1.7 0.4 0.0 2018-09-18 13:26:30.583216|

6 4.6 3.4 1.4 0.3 0.0 2018-09-18 13:26:30.583216|

7 5.0 3.4 1.5 0.2 0.0 2018-09-18 13:26:30.583216|

8 4.4 2.9 1.4 0.2 0.0 2018-09-18 13:26:30.583216|

9 4.9 3.1 1.5 0.1 0.0 2018-09-18 13:26:30.583216|

你知道时间戳有什么问题吗?

1 回复 | 直到 6 年前

Pradeep Khatri 6 年前

mimi,bteq脚本和数据文件中列的编号和顺序不匹配在下面的示例中检查脚本中指定的列和文件中的列数

"SEPAL_LENGTH" (1),  "SEPAL_WIDTH"(2),  "PETAL_LENGTH"(3),  "PETAL_WIDTH"(4),  "TARGET"(5),  "TIMESTAMP1"(6),  "NULL_VALUES"(7)

col1->0|  col2->5.1|  col3->3.5|  col4->1.4|  col5->0.2|  col6->0.0|  col7->2018-09-18 13:26:30.583216|  col8->

您可以通过在表的timestamp列之前再添加一列和bteq脚本来解决问题,如下所示,我在timestamp列之前添加了“target1”

USING "SEPAL_LENGTH" (varchar(2000)),  "SEPAL_WIDTH" (varchar(2000)),  "PETAL_LENGTH" (varchar(2000)),  "PETAL_WIDTH" (varchar(2000)),  "TARGET" (varchar(2000)),  "TARGET1" (varchar(2000)),  "TIMESTAMP1" (varchar(2000)),  "NULL_VALUES" (varchar(2000)) 
INSERT INTO IRIS_delete VALUES(cast( :"SEPAL_LENGTH"  as FLOAT), cast( :"SEPAL_WIDTH"  as FLOAT), cast( :"PETAL_LENGTH"  as FLOAT), cast( :"PETAL_WIDTH"  as FLOAT), cast( :"TARGET"  as FLOAT), cast( :"TARGET1"  as FLOAT), cast( :"TIMESTAMP1"  as TIMESTAMP(6) FORMAT 'YYYY-MM-DDBHH:MI:SS.S(6)'), cast( :"NULL_VALUES"  as FLOAT));