CREATE TABLE Failed. 3706

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.

CREATE TABLE Failed. 3706

Hi Team,

 

I am trying to make a table containing the last business day of the month but I get an error stating "CREATE TABLE Failed/ 3706: Syntax error expected something between ')' and ';' ".

 

I am running query as per below:

 

CREATE TABLE UDPFS.PK_XPOS_D

AS

(

SELECT CURRENT_DATE AS "CURRENT_DATE",

D1.CALR_MNTH_STRT_D - 1 AS EOM_D,

MAX(D2.CALR_D) AS LAST_BUSN_DAY_D

 

FROM P_TEC_0.DIMN_DATE D1

JOIN P_TEC_0.DIMN_DATE D2

ON D2.CALR_D BETWEEN D1.CALR_MNTH_STRT_D - 6

AND D1.CALR_MNTH_STRT_D - 1

AND D2.CALR_NON_WORK_DAY_F = 'N'

WHERE CURRENT_DATE = D1.CALR_D

 

GROUP BY 1,2

)

Seem's a very straight forward fix. I have never had this problem on SQL server or when creating a volatile table in Teradata.

 

Any help is appreciated.

 

Cheers,

PK
 

1 REPLY
Junior Contributor

Re: CREATE TABLE Failed. 3706

The DATA option is missing and there's no default :)

 

CREATE TABLE UDPFS.PK_XPOS_D
AS
(
SELECT CURRENT_DATE AS "CURRENT_DATE",
D1.CALR_MNTH_STRT_D - 1 AS EOM_D,
MAX(D2.CALR_D) AS LAST_BUSN_DAY_D
 
FROM P_TEC_0.DIMN_DATE D1
JOIN P_TEC_0.DIMN_DATE D2
ON D2.CALR_D BETWEEN D1.CALR_MNTH_STRT_D - 6
AND D1.CALR_MNTH_STRT_D - 1
AND D2.CALR_NON_WORK_DAY_F = 'N'

WHERE CURRENT_DATE = D1.CALR_D
 
GROUP BY 1,2
) WITH DATA

 

Btw, logic like this (first/last business day) should be another precalculated column in your calendar table.