View
帶有“With Clause”語法的 Teradata 視圖
我正在嘗試在 Teradata 中使用“With”子句查看視圖,但它不允許創建視圖。請建議任何選項。
下面給出的是我嘗試過的查詢:
create view derived_table(derived_column) AS ( With temp_table(a,b,c) As (select a,b,c from table_a where column1=column2) select (a||'-'||b) as derived_column from temp_table union all select (a||'-'||b||'-'||C) as derived_column from temp_table )
這很痛苦,但 Teradata 不支持視圖中的 CTE(截至 15.00),請參閱SQL 數據操作語言 > SELECT 語句 > WITH 和 WITH RECURSIVE 語句修飾符。
在您的情況下,您可以使用 CTE 的內容創建另一個視圖,但您可能已經知道這一點。
正如 Nickolay 在他的回答中解釋的那樣,
WITH
在 Teradata 的視圖定義中是不允許的。您可以改用派生表來解決特定問題。您可以指定它兩次,保留
union
您的查詢:create view derived_table (derived_column) as select (a||'-'||b) as derived_column from (select a,b,c from table_a where column1 = column2) as t union all select (a||'-'||b||'-'||c) from (select a,b,c from table_a where column1 = column2) as t ;
甚至更簡單:
create view derived_table (derived_column) as select (a||'-'||b) as derived_column from table_a where column1 = column2 union all select (a||'-'||b||'-'||c) from table_a where column1 = column2 ;
但不必指定兩次。您可以將其連接到另一個 2 行派生表並
case
在列表達式中使用:create view derived_table (derived_column) as select case opt.o when 1 then (a||'-'||b) when 2 then (a||'-'||b||'-'||c) end as derived_column from (select a,b,c from table_a where column1 = column2) as t cross join (select 1 as o union all select 2) as opt ;