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

Database
Enthusiast

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

Hi,

I am trying to create a view on top of a SQL:-

CREATE VIEW VIEWS_DB.sample_view AS

WITH POC (

AUDT_RULE_ID,

EVNT_DTM

)AS

(SEL AUDT_RULE_ID,

EVNT_DTM

FROM DB.TABLE_XX)

SEL * FROM POC

 

But the query issues an error:-

6926: WITH [RECURISVE] clause or recursive view is not supported within WITH [RECURISVE] definitions, views, trigger and stored procedures.

My question is : Can we create view on top of an SQL which starts with a "WITH" clause.

thanks in advance.

Sarvesh

21 REPLIES
Supporter

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

From the manual - 13.10 SQL Data Manipulation Language


WITH [RECURSIVE] Request Modifier


Purpose


Enables searching a table using iterative self-join and set operations.


The only SQL DML statement that can be modified with a WITH or WITH RECURSIVE


modifier is SELECT.


given that and your error message - which means the same - I would say no.

Senior Apprentice

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

Hi Sarvesh,

before TD14 WITH is not supported for views.

But as TD up to 13.10 only allows a single WITH, it's easy to rewrite as a Derived Table.

CREATE VIEW VIEWS_DB.sample_view AS
SEL *
FROM
(SEL AUDT_RULE_ID,
EVNT_DTM
FROM DB.TABLE_XX) POC

If it's supposed to be a recursive WITH then you have to use a different syntax, CREATE RECURSIVE VIEW.

Dieter

Enthusiast

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

Thank you ulrich and Dieter

Enthusiast

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

Hi,

I have one more question regarding the WITH clause.

In TD14, is it allowed to have with clause in derived tables.

e.g just for illustration purpose:-

SELECT

cast(f1 as char (10)),

cast(f2 as char(26))

From (

WITH POC (f1,f2) as (Sel f1,f2 from table x)

Select f1,f2 from POC

) temp

Supporter

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

What do you need this for???

You could use a straight forward derived table


SELECT


cast(f1 as char (10)),


cast(f2 as char(26))


From (


Sel f1,f2 from table x


) temp

Enthusiast

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

Thanks Ulrich...

But as stated in the earlier post, i have given the example just for illustration purpose.

I am still looking for the answer of :- In TD14, is it allowed to have derived tables starting with the WITH clause?

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

Regarding the first question ... about using WITH in a CREATE VIEW ...

The v14.00 doc for CREATE VIEW says it is valid ...

http://www.info.teradata.com/HTMLPubs/DB_TTU_14_00/index.html#page/SQL_Reference/B035_1144_111A/Crea...

But I get the same error as sarvesh.

I agree with Ulrich's comment ... but that specifically says DML, not DDL.

Therefore, I believe it should work.

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

Hi, I'm not able to Create view(Non Recursive) using WITH cluase for derived table going by the syntax given in the above link.

Replace View DB_Name.Vw_Name AS

WITH Temp(C1,C2,C3)

AS ( SEL A,B,C From TABLE table_a)

Sel C1,C2,C3,D

from Temp tmp Inner join table_b B

On tmp.C1 = B.C1;

This is just a sample view. Actually there are multiple Unions in the view with the derived table in WITH clause used in every union. Please guide how can this be achieved.

I got this error while creating the view: 6926 WITH (RECURSIVE) clause or recursive view is not supported within WITH(RECURSIVE) efinitions, views, triggers or stored procedures

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

Since this topic was revived I'll try to ask a related question too. I'm using the "td_normalize_overlap_meet" function to join rows that overlap (and/or meet) for a time period. A single query works fine, but I haven't been able to write the query as a view. Is that at all possible?

Code that works fine as a single query:

WITH tt(Rp_Id, active_period) AS 
(
SELECT
Rp_Id
, PERIOD(Vld_Fm_Tms - INTERVAL '14' DAY, Vld_To_Tms) AS active_period

FROM
DB.Table_Agreement
)
SELECT *
FROM TABLE( td_sysfnlib.td_normalize_overlap_meet(NEW VARIANT_TYPE(tt.Rp_Id), tt.active_period)
RETURNS ( Rp_Id INTEGER, active_period PERIOD(TIMESTAMP(0)))
HASH BY Rp_Id
LOCAL ORDER BY tt.Rp_Id, tt.active_period)
AS tt2(Rp_Id, active_period)

I can create a table adding a CREATE TABLE DB.NEW_TBL AS statement, but CREATE VIEW DB.NEW_VIEW does not work.

I'm on TD 14.10.