将CTE表值插入物理表时出错 [英] Error when inserting CTE table values into physical table

问题描述

我有一个复杂的查询，可以从其他CTE_Tables创建一个主CTE_Table。我想将主CTE_Table的结果插入物理表中。我正在使用Teradata 15.10.04.03版

I have a complex query that creates a master CTE_Table form other CTE_Tables. I want to insert the results of the master CTE_Table into a physical table. I'm using Teradata version 15.10.04.03

选择失败。 [3707]语法错误，可能是类似'SELECT'关键字或'（'或'TRANSACTIONTIME'关键字或'）'和'INSERT'关键字之间的'VALIDTIME'关键字之类的东西。

SELECT Failed. [3707] Syntax error, expected something like a 'SELECT' keyword or '(' or a 'TRANSACTIONTIME' keyword or a 'VALIDTIME' keyword between ')' and the 'INSERT' keyword.

DROP TABLE dbname.physicalTablename;

DROP TABLE dbname.physicalTablename ;

创建多表dbname.physicalTablename，
不会回退，
之前没有期刊，
之后没有期刊，
CHECKSUM =默认，
默认合并比例
（
col1整数，
col2整数，
col3整数
）
没有主索引;

CREATE MULTISET TABLE dbname.physicalTablename , NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT, DEFAULT MERGEBLOCKRATIO ( col1 INTEGER, col2 INTEGER, col3 INTEGER ) NO PRIMARY INDEX ;

其中

cteTable3 AS
（选择A. colA，A.colB，A.colC，B.col1，B.col2，B.col3
from cteTable1 A INNER JOIN cteTable2 ON（blah blah blah）），

cteTable3 AS ( SELECT A.colA, A.colB, A.colC, B.col1, B.col2, B.col3 FROM cteTable1 A INNER JOIN cteTable2 ON (blah blah blah) ),

cteTable2 AS
（SELECT col1，col2，col3 FROM SourceTableB），

cteTable2 AS ( SELECT col1, col2, col3 FROM SourceTableB ),

cteTable1 AS
（SELECT colA，colB，colC FROM SourceTableA）

cteTable1 AS ( SELECT colA, colB, colC FROM SourceTableA )

插入dbname.physicalTablename
（col1，col2，col3，col4，col5， col6）

选择
（C3.colA，C3.colB，C3.colC，C3.col1，C3.col2，C3.col3）
来自cteTable3 C3;

INSERT INTO dbname.physicalTablename ( col1, col2, col3, col4, col5, col6 )
SELECT (C3.colA, C3.colB, C3.colC, C3.col1, C3.col2, C3.col3) FROM cteTable3 C3 ;

推荐答案

虽然您缺少问题的INSERT部分，但我认为以下内容可能会解决问题。在INSERT中使用CTE的正确格式为：

While you are missing the INSERT portion of the question, I think the following might clear things up. The correct format for using a CTE in an INSERT is:

INSERT INTO <tablename>
WITH <cte> AS (SELECT...)
SELECT <fields> FROM <cte>

请考虑以下内容：

CREATE MULTISET VOLATILE TABLE tmp AS (SELECT 'bobby' as firstname) WITH DATA ON COMMIT PRESERVE ROWS;

INSERT INTO tmp
WITH cte AS (select 'carol' as firstname)
SELECT * FROM cte;

SELECT * FROM tmp;

DROP TABLE tmp;

这篇关于将CTE表值插入物理表时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！