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
)

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 ;