0 - 7 of 7 tags for cte

Normally when you define a column and give it an alias you can reference it in calculating another column:

Can anyone please explain difference between Common Table Expression(CTE)Tables and Volatile tables in which scenario they perform better.
As we can create & drop volatile tables as part of our SQL, where exactly CTE will perform better while compare to Volatile tables?
Thank You.

I have a table t in which there are various attribute like product,year,qtr,month,sale. I have to calculate the avg_qtr sale and show previous avg_qtr sale. I have used windowing function to show previous avg_qtr sale. I have read about lag but here it is not possible to use as it is not fixed after how many rows it will be repeated.

The Teradata 14.0 DDL manual shows that you can do the following, using a common table

create view rdptest

as

(

with test1 (clnt_cd,clnt_id)

as (select clnt_cd,clnt_id from dbatest_core_tb.clnt)

select clnt_cd,clnt_id from test1

)

;

 

Hello
I'm trying to create a view from WITH ... SELECT query.  The query itself works fine but once I try to create a view from it, I get a 3707 error.  
I'm running off TD 14.10.  Any thoughts?
Thanks, Richard

I'm curious to see if there is a way to get period-to-date values (e.g. WTD, MTD, QTD, YTD) without using a CTE or subquery. Here is an example query using a CTE:

Is it possible to put a common table expression within a view?

The following SQL works fine

WITH myCTE (account_number) AS
(
SELECT account_number
FROM dbname.tablename
WHERE columnname = 'somevalue'
)

SELECT account_number
FROM myCTE;

However when I try to put this into a view . . .

CREATE VIEW myCTE_VIEW AS
(

WITH myCTE (account_number) AS
(
SELECT account_number
FROM dbname.tablename
WHERE columnname = 'somevalue'
)

SELECT account_number
FROM myCTE

)