Common Table Expression Within View

Database

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
4 REPLIES

Re: Common Table Expression Within View

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.

 

Re: Common Table Expression Within View

Same problem here... Is there an answer?

Teradata Employee

Re: Common Table Expression Within View

What release are you running?

This capability was allowed in TD14.0 and future releases.

Re: Common Table Expression Within View

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.