CREATE TABLE Failed 3706: syntax error: expected something between ')' and the word 'c'.

Analytics
Enthusiast

CREATE TABLE Failed 3706: syntax error: expected something between ')' and the word 'c'.

Hi Team,

Trying to execute the following code, ending up with an error....Please suggest

 

CREATE MULTISET VOLATILE TABLE vt_email AS(

SEL  cast(( a.dlv_dt(format 'YYYYMM')) as char(6)) AS Delv_Month, b.alloc_id, SUM(dlv) as Total_Dlv

FROM UD123.UCA380_email_data a

INNER JOIN  ud123.uca380_email_dim b

ON a.email_id = b.email_id

WHERE b.alloc_id IN ('360_xb','bank_xb', 'coaf_xb', 'inv_xb')  GROUP BY 1,2)  c 

Full JOIN (Sel Delv_Month, booking_lob, count (*) as Total_Bookings

from NRR_data1

WHERE Delv_Month IN (201602, 201603, 201604, 201607, 201608, 201609, 201610) Group by 1,2) d

ON  c.Delv_Month = d.Delv_Month and c.alloc_id = d. booking_lob ) 

WITH DATA PRIMARY INDEX(c.Delv_Month) ON COMMIT PRESERVE ROWS;

 

Please check and Suggest

 

2 REPLIES
Teradata Employee

Re: CREATE TABLE Failed 3706: syntax error: expected something between ')' and the word 'c'.

"c" means nothing in this query.  The problem is in this line:

WHERE b.alloc_id IN ('360_xb','bank_xb', 'coaf_xb', 'inv_xb')  GROUP BY 1,2)  c

 

It should be:

WHERE b.alloc_id IN ('360_xb','bank_xb', 'coaf_xb', 'inv_xb')  GROUP BY 1,2)

 

And then

... PRIMARY INDEX(Delv_Month) ...

Teradata Employee

Re: CREATE TABLE Failed 3706: syntax error: expected something between ')' and the word 'c'.

the query is a bit of a mess: either the whole part from "c" to "WITH DATA" shouldn't be there, or you want the "c" part to be representative of a derived table which you are then joining to "d", in which case you need an outer SELECT.

 

SELECT d.whatever, c.whatever, c.Delv_Month

FROM (

   SEL cast(( a.dlv_dt ...

   ... ) GROUP BY 1,2 ) c

FULL JOIN ( ... ) d

[remove WITH DATA]

PRIMARY INDEX(Delv_Month)
ON COMMIT PRESERVE ROWS;