All Forums Database
sarvesh_kumar 6 posts Joined 03/11
10 Jul 2012
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

ulrich 816 posts Joined 09/09
10 Jul 2012

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.

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

dnoeth 4628 posts Joined 11/04
10 Jul 2012

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

Dieter

sarvesh_kumar 6 posts Joined 03/11
10 Jul 2012

Thank you ulrich and Dieter

sarvesh_kumar 6 posts Joined 03/11
07 Aug 2012

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

ulrich 816 posts Joined 09/09
08 Aug 2012

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

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

sarvesh_kumar 6 posts Joined 03/11
22 Aug 2012

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?

jrgchip 4 posts Joined 05/15
10 Jun 2015

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/Create_Transform-Syntax.025.78.html#ww16763403
 
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.

Viswanath1987 3 posts Joined 02/14
06 Aug 2015

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

olekanin 2 posts Joined 07/12
06 Aug 2015

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.

Viswanath1987 3 posts Joined 02/14
07 Aug 2015

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 !!

Viswanath1987 3 posts Joined 02/14
10 Aug 2015

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

ToddAWalter 316 posts Joined 10/11
10 Aug 2015

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.

You must sign in to leave a comment.