creating a view on a SQL query starting with "WITH" clause

Database

Re: creating a view on a SQL query starting with "WITH" clause

Yes, the same problem. Could execute the WITH part as a simple SQL. But when I create the same as a view it throws as error.

Can you guys please guide on this. Thanks in Advance !!

Re: creating a view on a SQL query starting with "WITH" clause

Hi Dieter, can you please help with this view creation using 'WITH' keyowrd for derived tables ?

Thanks !!

Teradata Employee

Re: creating a view on a SQL query starting with "WITH" clause

Common Table Expressions (WITH clause) is not allowed in views or derived tables. This functionality is on the to-do list but has not made it to the top yet.

Enthusiast

Re: creating a view on a SQL query starting with "WITH" clause

This is by far the most painful thing in converting from Oracle.

 

A view CAN NOT contain WITH clause.  Ugh!!!!  Makes sticking with Oracle tempting.

 

 

Teradata Employee

Re: creating a view on a SQL query starting with "WITH" clause

Why not use derived tables or views?

Enthusiast

Re: creating a view on a SQL query starting with "WITH" clause

I want to.   Once you use With the SQL is soooo much easier to read.... but TD is old schoool I guess.  (fast but old school) 

 

 

Create view bigcomplexsql as (

with xx as (select xxxx

from tab

where xxxx) 

yy as (select yyyy 

from taby

where yyyy)

select *

from tabqq

join xx on() 

jioin yy on ()

);

 

Highlighted
Teradata Employee

Re: creating a view on a SQL query starting with "WITH" clause

Create view bigcomplexsql as (
select *
from tabqq
join (select xxxx
     from tab
     where xxxx) xx
on ...
join (select yyyy
     from taby
     where yyyy) yy
on ...
);

 

Same amount of code.  Derived tables are a pain only when they are used more than once in a query; in that case it's nice if you can create a view to use instead of writing the DT Select twice.

Enthusiast

Re: creating a view on a SQL query starting with "WITH" clause

Ahhh .. yeah we get that, of course.

 

Like I said you have not started using WITH (especially when the same clause is used many times)... you will never go back.

 

the fact CONNECT BY <> RECUSIVE VIEW since it has the same limitation is preventing us from using TD for app development.

 

 

Teradata Employee

Re: creating a view on a SQL query starting with "WITH" clause

Yes, the inability to create a view of a recursive view is a problem.  But there are solutions, and there are people in Teradata Professional Services who have dealt with this limitation.  Given your serious concern, your local PS team would probably be glad to help.

Enthusiast

Re: creating a view on a SQL query starting with "WITH" clause

Don't get me wrong - I love Teradata - loev the power.  But there are a few things that just kill us.