All Forums General
sulliv16 6 posts Joined 04/16
13 Apr 2016
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' );

 

sulliv16 6 posts Joined 04/16
13 Apr 2016

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

ToddAWalter 316 posts Joined 10/11
13 Apr 2016

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

sulliv16 6 posts Joined 04/16
14 Apr 2016

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

sulliv16 6 posts Joined 04/16
14 Apr 2016

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

ToddAWalter 316 posts Joined 10/11
14 Apr 2016

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

You must sign in to leave a comment.