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 ;