Teradata SQL error 3706

General
Enthusiast

Teradata SQL error 3706

I am receiving an error in SQL Assistant: CREATE TABLE Failed.  [3706] Syntax error: expected something between ')' and ';'. Can someone point me to where the error is? Here is my code (I've highlighted where SQL Assistant is telling me where the issue is

create table dl_pn_pricing.MAC_1 as (
select *
from (select
a.EDW_PHRM_CLM_TRANS_ID
, a.PHRM_DIM_ID
, a.PRSCBR_DIM_ID
, a.MBR_DIM_ID
, c.RXCLM_CARR_ID
, c.RXCLM_ACCT_ID
, c.RXCLM_GRP_ID
, c.RXCLM_MBR_ID
, a.PAT_AGE_NUM
, a.SUBM_DOB_DT
, b.CALNDR_DT
, a.PHRM_CLM_SRC_1_ID
, a.PHRM_CLM_SRC_2_ID
, a.CLM_STAT_CD
, a.PAID_CLM_CNT
, d.GPI
, d.GPI_GENRC_NME
, d.PROD_NME
, d.PROD_NME_EXT
, d.PROD_DESC_ABBR
, d.MTRC_STR_NUM
, d.STR_UOM_CD
, a.DSPNSD_PRC_QTY
, a.DAY_SPLY_CNT
, a.DAY_SPLY_CNT + b.CALNDR_DT -1 AS SUPPLY_TO
, a.SUBM_PROD_ID_QLFR_CD
, a.SUBM_PROD_ID
, a.SUBM_PRSCBR_ID_QLFR_CD
, a.SUBM_PRSCBR_ID
, f.PRSCBR_NPI
, e.PHRM_NCPDP
, e.PHRM_NPI

/* DW FINAL CLAIM LOGIC */
, Case a.CLM_STAT_CD When 'R' Then 2 Else 1 End As ForceDownReject
, Case a.CLM_STAT_CD When 'P' Then 2 When 'C' Then 3 When 'X' Then 1 Else 4 End As TransactionOrder
, ROW_NUMBER() OVER (PARTITION BY a.PHRM_CLM_SRC_1_ID ORDER BY ForceDownReject, a.PHRM_CLM_SRC_2_ID, TransactionOrder) As RowNumber
, Case When RowNumber = 1 And a.CLM_STAT_CD = 'P' Then 'Y' Else 'N' End As Dw_Final_Paid

From P_EDW_DIST_VIEW.PHRM_CLM_ALL_TRANS_FACT a

Inner Join P_EDW_DIST_VIEW.PHRMTCL_PROD_DIM d On a.PHRMTCL_PROD_DIM_ID = d.PHRMTCL_PROD_DIM_ID
Inner Join P_EDW_DIST_VIEW.CALNDR_DT_DIM b On a.CLM_DOS_FILL_DT_DIM_ID = b.CALNDR_DT_DIM_ID
Inner Join P_EDW_DIST_VIEW.MBR_DIM c On a.MBR_DIM_ID = c.MBR_DIM_ID
Inner Join P_EDW_DIST_VIEW.PHRM_DIM e On a.PHRM_DIM_ID = e.PHRM_DIM_ID
Inner Join P_EDW_DIST_VIEW.PRSCBR_DIM f On a.PRSCBR_DIM_ID = f.PRSCBR_DIM_ID
) t1
WHERE Dw_Final_Paid = 'Y' );
Tags (1)
5 REPLIES
Enthusiast

Re: Teradata SQL error 3706

The highlight didn't work, but the place SQL Assistant tells me the error is that very last part of the code: = 'Y');

Teradata Employee

Re: Teradata SQL error 3706

WITH [NO] DATA is required on a CT AS.

Enthusiast

Re: Teradata SQL error 3706

@ToddAWalter I'm not sure that I follow. Is that an option I would place somewhere in the code?

Thanks

Enthusiast

Re: Teradata SQL error 3706

@ToddAWalter Nevermind, I figured it out! Thank you!

Teradata Employee

Re: Teradata SQL error 3706

As the manual shows ... It goes after the closing paren for the select before the final ";".