Teradata view with "With Clause" syntax

Question

I am trying to a view with "With" clause in Teradata but it is not allowing to create a view. Please suggest with any options.

Given below is the query which I have tried:

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
)

• Mat: You should probably edit the error message(s) you're getting into your question.

Answer 1

It's a pain, but Teradata doesn't support CTE in views (as of 15.00), see SQL Data Manipulation Language> The SELECT Statement> WITH and WITH RECURSIVE Statement Modifiers.

In your case you can create another view with the contents of the CTE, but you probably know that already.

Answer 2

As Nickolay explained in his answer, WITH is not allowed in a view definition in Teradata.

You can work around the specific problem by using a derived table instead. You can either specify it twice, keeping the union of your query:

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 ;

or even simpler:

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 ;

But it doesn't have to be specified twice. You could join it to another 2-rows derived table and use case in the column expression:

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 ;