All Forums Database
ubya308 4 posts Joined 05/11
03 Jun 2011
Common Table Expression Within View

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

)

I get the error code "CREATE VIEW Failed. 3707: Syntax Error, expected something like a 'SELECT' keyword or '(' between then '(' and the 'WITH' keyword."

Is this possible at all, and if so what am I doing wrong?

Thanks

Mike

ddracod 1 post Joined 04/13
16 Apr 2013

Hi ~ I'm wondering if someone had answered this question, because I'm running into the same problem. To simplify the code, this is what I'm trying to do and getting the same error message:
CREATE VIEW EDW_CPD.vw_PMC_TEST AS
(
WITH TEST (LABEL) AS
(
SELECT 'TEST' AS LABEL
)
SELECT *
FROM TEST
)
Any help is appreciated.
Thank you!
D.
 
 
 

eric.lord 1 post Joined 05/09
27 Jun 2013

Same problem here... Is there an answer?

ToddAWalter 316 posts Joined 10/11
27 Jun 2013

What release are you running?
This capability was allowed in TD14.0 and future releases.

Kiran KT 1 post Joined 10/15
30 Oct 2015

I am using Teradata 15.00.03.06, doesn't work for me. I am able use CTE in query but not in a view.

You must sign in to leave a comment.