2

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
)
1
  • 3
    You should probably edit the error message(s) you're getting into your question. – Mat Oct 28 '15 at 16:39
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.

1

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 ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.