create table as - Failed 3706 Syntax error: expected something between ')' and the ';'.

Database
Fan

create table as - Failed 3706 Syntax error: expected something between ')' and the ';'.

Hi all,

I am trying to create a table for the results of my select query as I need to run some analysis on them.

The select ran and I have around 60k rows in the result. When I try to create a table, it provides me with error 3706.

Can you please help?

Thanks,

Dieter

CREATE TABLE user_work.GHH_UK_RET
AS (
SEL A.REGIS_PRSNA_ID,
A.MKTNG_PGM_NBR,
CASE
WHEN CAST(E.CNSMR_CHCE_DATETM AS DATE FORMAT 'YYYY-MM-DD') <= CURRENT_DATE-STAT_NBR_DAYS THEN 'Y'
ELSE 'N'
END AS OPT_LOSS
FROM icrm.REGIS_PRSNA_EMAIL_ADDR A
INNER JOIN RTN_RULES_DTL B
ON A.MKTNG_PGM_NBR=B.MKTNG_PGM_NBR
AND B.RTN_RULE_ID=3
AND B.MKTNG_PGM_NBR = 288 --AND COALESCE(B.SYS_ENT_STATE,'') = 'ACTIVE'
INNER JOIN RTN_RULES C
ON B.RTN_RULE_ID=C.RTN_RULE_ID
AND COALESCE(C.SYS_ENT_STATE,'') = 'ACTIVE'
INNER JOIN (
SEL
A.MKTNG_PGM_NBR,
A.REGIS_PRSNA_ID,
CAST(MAX(CAST(CNSMR_CHCE_DATETM AS VARCHAR(10))) AS DATE FORMAT 'YYYY-MM-DD') CNSMR_CHCE_DATETM
FROM(
SEL
A.MKTNG_PGM_NBR,
A.REGIS_PRSNA_ID,
MAX(CNSMR_CHCE_DATETM)CNSMR_CHCE_DATETM
FROM icrm.REGIS_PRSNA_EMAIL_ADDR A
WHERE CNSMR_CHCE_DATETM IS NOT NULL
AND EMAIL_STATUS_CODE <> 'ER'
GROUP BY 1,2
UNION ALL
SEL
A.MKTNG_PGM_NBR,
A.REGIS_PRSNA_ID,
MAX(CAST(LATST_ACTVY_DATE AS TIMESTAMP(6))) CNSMR_CHCE_DATETM
FROM icrm.REGIS_PRSNA A
GROUP BY 1,2
UNION ALL
SEL
A.MKTNG_PGM_NBR,
A.REGIS_PRSNA_ID,
MAX(CAST(RESP_DATETM AS TIMESTAMP(6))) CNSMR_CHCE_DATETM
FROM mdm.EMAIL_CMPGN_RESP A
GROUP BY 1,2
UNION ALL
SELECT
D.MKTNG_PGM_NBR,
D.REGIS_PRSNA_ID,
MAX(CAST(CNSMR_ACTN_START_DATETM AS TIMESTAMP(6))) CNSMR_CHCE_DATETM
FROM icrm.CNSMR_ACTN D
WHERE CNSMR_ACTN_START_DATETM IS NOT NULL
AND D.REGIS_PRSNA_ID IS NOT NULL
GROUP BY 1,2
UNION ALL
SEL
A.MKTNG_PGM_NBR,
A.REGIS_PRSNA_ID,
MAX(CAST(LYLTY_TRANX_DATETM AS TIMESTAMP(6))) CNSMR_CHCE_DATETM
FROM icrm.REGIS_PRSNA A
INNER JOIN LYLTY_TRANX D
ON A.LYLTY_PGM_NBR=D.LYLTY_PGM_NBR
AND A.LYLTY_ACCT_NUM=D.LYLTY_ACCT_NUM
AND A.MKTNG_PGM_NBR=D.MKTNG_PGM_NBR
GROUP BY 1,2
UNION ALL
SELECT
A.MKTNG_PGM_NBR,
A.REGIS_PRSNA_ID,
MAX(CAST(A.CAMPAIGN_RUN_DTTM AS TIMESTAMP(6))) CNSMR_CHCE_DATETM
FROM
mdm.CAMPAIGN_SGMNT_CNSMR A
INNER JOIN COMM_CHNL D
ON A.COMM_CHNL_CODE=D.COMM_CHNL_CODE
WHERE D.COMM_CHNL_NAME IN ('Email','All')
GROUP BY 1,2
UNION ALL
SEL
EXCM.MKTNG_PGM_NBR,
L.REGIS_PRSNA_ID,
MAX(CAST(Selection_Dttm AS TIMESTAMP(6))) CNSMR_CHCE_DATETM
FROM mdm.LH_REALIZED_LEAD_HIST L
JOIN trm_meta_db.EX_COMMUNICATION EXCM
ON L.COMMUNICATION_ID = EXCM.COMMUNICATION_ID
AND EXCM.INITIATIVE_TYPE NOT IN ('Cancelled','Test',
'Extract Non-Campaign Purpose')
JOIN trm_meta_db.CM_COMMUNICATION C
ON C.COMMUNICATION_ID = L.COMMUNICATION_ID
JOIN trm_meta_db.CM_COMMUNICATION_COMM_PLAN P
ON C.COMMUNICATION_ID = P.COMMUNICATION_ID
JOIN trm_meta_db.CM_COMM_PLAN_STEP S
ON P.COMM_PLAN_ID = S.COMM_PLAN_ID
JOIN trm_meta_db.CM_COMM_PLAN_MESSAGE M
ON S.COMM_PLAN_ID = M.COMM_PLAN_ID
AND S.STEP_ID = M.STEP_ID
JOIN trm_meta_db.CM_MESSAGE ME
ON M.MESSAGE_ID = ME.MESSAGE_ID
JOIN trm_meta_db.CM_CHANNEL_CLASS CH
ON ME.DEFAULT_CHANNEL_CLASS_ID = CH.CHANNEL_CLASS_ID
AND CH.NAME = 'Email Channel Class'
JOIN trm_meta_db.CM_CHANNEL_INSTANCE CI
ON ME.DEFAULT_CHANNEL_INSTANCE_ID = CI.CHANNEL_INSTANCE_ID
GROUP BY 1,2
UNION ALL
SEL
EXCM.MKTNG_PGM_NBR,
L.REGIS_PRSNA_ID,
MAX(CAST(Selection_Dttm AS TIMESTAMP(6))) CNSMR_CHCE_DATETM
FROM trm_lead_db.LH_REALIZED_LEAD L
JOIN trm_meta_db.EX_COMMUNICATION EXCM
ON L.COMMUNICATION_ID = EXCM.COMMUNICATION_ID
AND EXCM.INITIATIVE_TYPE NOT IN ('Cancelled','Test',
'Extract Non-Campaign Purpose')
JOIN trm_meta_db.CM_COMMUNICATION C
ON C.COMMUNICATION_ID = L.COMMUNICATION_ID
JOIN trm_meta_db.CM_COMMUNICATION_COMM_PLAN P
ON C.COMMUNICATION_ID = P.COMMUNICATION_ID
JOIN trm_meta_db.CM_COMM_PLAN_STEP S
ON P.COMM_PLAN_ID = S.COMM_PLAN_ID
JOIN trm_meta_db.CM_COMM_PLAN_MESSAGE M
ON S.COMM_PLAN_ID = M.COMM_PLAN_ID
AND S.STEP_ID = M.STEP_ID
JOIN trm_meta_db.CM_MESSAGE ME
ON M.MESSAGE_ID = ME.MESSAGE_ID
JOIN trm_meta_db.CM_CHANNEL_CLASS CH
ON ME.DEFAULT_CHANNEL_CLASS_ID = CH.CHANNEL_CLASS_ID
AND CH.NAME = 'Email Channel Class'
JOIN trm_meta_db.CM_CHANNEL_INSTANCE CI
ON ME.DEFAULT_CHANNEL_INSTANCE_ID = CI.CHANNEL_INSTANCE_ID
GROUP BY 1,2
) A
GROUP BY 1,2) E
ON A.REGIS_PRSNA_ID = E.REGIS_PRSNA_ID
AND A.MKTNG_PGM_NBR = E.MKTNG_PGM_NBR
INNER JOIN icrm.REGIS_PRSNA F
ON A.REGIS_PRSNA_ID = F.REGIS_PRSNA_ID
AND A.MKTNG_PGM_NBR = F.MKTNG_PGM_NBR
and f.MKTNG_PGM_NBR = 288
WHERE A.SUBSCRPTN_OPT_NBR IS NOT NULL
AND A.SUBSCRPTN_OPT_IND = 'I'
AND A.EMAIL_STATUS_CODE = 'AC'
AND F.PRSNA_STATUS_CODE='AC'
AND A.VALID_CNTCT_POINT_IND='Y'
AND OPT_LOSS='Y'
--GROUP BY 1,2,3
)
Tags (2)
2 REPLIES
Enthusiast

Re: create table as - Failed 3706 Syntax error: expected something between ')' and the ';'.

Pls add " WITH DATA " in the end and run the code.

Below is the syntax

Creat Table <db_name>.<table_name>

AS

(

<SQL>

)

WITH DATA ;

Enthusiast

Re: create table as - Failed 3706 Syntax error: expected something between ')' and the ';'.

u need to give WITH DATA in the end after your select clause ends..

CREATE TABLE A AS(SEL * FROM B)WITH DATA