Create volatile table failed 3707 expected Unicode delimited identifier...

Database

Create volatile table failed 3707 expected Unicode delimited identifier...

Hi-

I'm trying to learn how to create a temporary table and am getting this error message

CREATE TABLE Failed 3707: Syntax error, expected something like a name or a Unicode delimited identifier or an 'UDFCALLNAME' keyword between ')' and the 'WITH' keyword.

Here is the code. What do I need to fix? Thank you-

CREATE VOLATILE TABLE STRENLR.TriMedAdm AS

(Select

ClnSorCd,

RefNbr,

MembKey,

SrcCaseTypeCd,

PlaceSrvSumDesc,

AdmDt,

PrimDxCd,

ActlLOSNbr

 

From

( Select

REQ.CLNCL_SOR_CD As ClnSorCd,

REQ.RFRNC_NBR As RefNbr,

REQ.MBR_KEY As MembKey,

REQ.SRC_CASE_TYPE_CD As SrcCaseTypeCd,

REQ.PLACE_OF_SRVC_SMRY_TXT As PlaceSrvSumDesc,

REQ.ADMT_DT As AdmDt,

REQ.PRMRY_DIAG_CD As PrimDxCd,

REQ.ACTL_LOS_NBR As ActlLOSNbr

From UM_RQST REQ

Where (REQ.CLNCL_SOR_CD IN ('870') and REQ.MBRSHP_SOR_CD IN ('868','869', '886'))

and REQ.ADMT_DT = '2013-07-24'

and REQ.CASE_TYPE_CD In ('MED','SUR','MAT')

and REQ.AUTHRZN_STTS_CD = 'A'

and REQ.APRVD_INPAT_DAYS_NBR > 0

)

)

WITH DATA

PRIMARY INDEX (MembKey)

ON COMMIT PRESERVE ROWS;

 

select *

from STRENLR.TriMedAdm ;

Tags (1)
1 REPLY
Teradata Employee

Re: Create volatile table failed 3707 expected Unicode delimited identifier...

The "derived table expression" in the FROM clause must have a name, e.g.

and REQ.APRVD_INPAT_DAYS_NBR > 0

) MyDT

)

WITH DATA

But the outer SELECT isn't adding any value. Why not simplify?

CREATE VOLATILE TABLE STRENLR.TriMedAdm AS

( Select

REQ.CLNCL_SOR_CD As ClnSorCd,

REQ.RFRNC_NBR As RefNbr,

REQ.MBR_KEY As MembKey,

REQ.SRC_CASE_TYPE_CD As SrcCaseTypeCd,

REQ.PLACE_OF_SRVC_SMRY_TXT As PlaceSrvSumDesc,

REQ.ADMT_DT As AdmDt,

REQ.PRMRY_DIAG_CD As PrimDxCd,

REQ.ACTL_LOS_NBR As ActlLOSNbr

From UM_RQST REQ

Where (REQ.CLNCL_SOR_CD IN ('870') and REQ.MBRSHP_SOR_CD IN ('868','869', '886'))

and REQ.ADMT_DT = '2013-07-24'

and REQ.CASE_TYPE_CD In ('MED','SUR','MAT')

and REQ.AUTHRZN_STTS_CD = 'A'

and REQ.APRVD_INPAT_DAYS_NBR > 0

)

WITH DATA

PRIMARY INDEX (MembKey)

ON COMMIT PRESERVE ROWS;