How to create a table in CTE in Teradata

Database

How to create a table in CTE in Teradata

I am trying to create a table in CTE named CATERGORYLIST in CTE. I used the below query

WITH CATERGORYLIST AS

(

                                 SELECT '1 Response' AS CATEGORY

                                 UNION ALL

                                 SELECT '2 Response' 

                                 UNION ALL

                                 SELECT '3 Response' 

                             )

When I execute the expression, I get the following error.

"Error - Statement 1: SELECT Failed. 3707:  Syntax error, expected something like a 'SELECT' keyword or '(' or a 'VALIDTIME' keyword or a 'NONTEMPORAL' keyword between ')' and ';'."

Tags (1)
3 REPLIES
Teradata Employee

Re: How to create a table in CTE in Teradata

You can't execute a CTE by itself, it can only be prefixed to a SELECT statement.

Re: How to create a table in CTE in Teradata

when I execute with select statement, I still get the same error.

Teradata Employee

Re: How to create a table in CTE in Teradata

You have a secondary problem, in that UNION demands a FROM clause. But that would not give the same error.

What version of TD?

WITH CATERGORYLIST AS

(

SELECT * FROM (SELECT '1 Response' AS CATEGORY) x

UNION ALL

SELECT * FROM (SELECT '2 Response' AS CATEGORY) x

UNION ALL

SELECT * FROM (SELECT '3 Response' AS CATEGORY) x

)

SELECT * FROM CATERGORYLIST;