在MSSQL 2008中使用多个CTE时,我通常使用逗号进行分隔。
但是在Teradata环境中尝试这样做时,语法会出现错误。
在MS SQL中可以正常工作:
WITH CTE1 AS
(SELECT TOP 2 Name FROM Sales.Store)
,CTE2 AS
(SELECT TOP 2 ProductNumber, Name FROM Production.Product)
,CTE3 AS
(SELECT TOP 2 Name FROM Person.ContactType)
SELECT * FROM CTE1,CTE2,CTE3
现在,尝试将其转换为Teradata语法:
WITH RECURSIVE CTE1 (Name) AS
(SELECT TOP 2 Name FROM Sales.Store)
,RECURSIVE CTE2 (ProductNumber, Name) AS
(SELECT TOP 2 ProductNumber, Name FROM Production.Product)
,RECURSIVE CTE3 (Name) AS
(SELECT TOP 2 Name FROM Person.ContactType)
SELECT *
FROM CTE1,CTE2,CTE3
语法错误,期望在“,”和“RECURSIVE”关键字之间提供类似名称或Unicode限定符的内容。
第二次尝试(不使用多个RECURSIVE)。
WITH RECURSIVE CTE1 (Name) AS
(SELECT TOP 2 Name FROM Sales.Store)
,CTE2 (ProductNumber, Name) AS
(SELECT TOP 2 ProductNumber, Name FROM Production.Product)
,CTE3 (Name) AS
(SELECT TOP 2 Name FROM Person.ContactType)
SELECT *
FROM CTE1,CTE2,CTE3
注:本句为IT技术相关内容,意思为不允许同时定义多个WITH语句。不支持多个WITH定义。