All Forums Database
ydeepthireddy 2 posts Joined 10/15
08 Oct 2015
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 ';'."

Fred 1096 posts Joined 08/04
08 Oct 2015

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

ydeepthireddy 2 posts Joined 10/15
09 Oct 2015

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

Fred 1096 posts Joined 08/04
09 Oct 2015

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;

You must sign in to leave a comment.