Share via

SSIS ODBC TERADATA source - CTE syntax error

Gopinath Desai 46 Reputation points
2021-01-15T20:29:41.957+00:00

Hi All,
I am trying to pull data from Teradata into SQL Server using SSIS (ODBC source) and have written CTE sql query to it. Wile running on server below is syntax error i see

Error: Open Database Connectivity (ODBC) error occurred. state: '42000'. Native Error Code: -3707. [Teradata][ODBC Teradata Driver][Teradata Database] Syntax error, expected something like an 'EXCEPT' keyword or an 'UNION' keyword or a 'MINUS' keyword between a string or a Unicode character literal and ';'.

Please let me know the mistake am doing here.

WITH amt AS (
Select distinct udw_med_clm_id, udw_adjd_mce_id, adjd_med_clm_srvc_ln_num,
proc_dt, orig_src_sys_cd, amt_typ_cd, src_amt
from udwbasesecureview1.adjd_mce_srvc_src_amt
where src_row_expir_dt = '9999-12-31' and src_row_sts_cd = 'A'
and orig_src_sys_cd = 'APF'
and amt_typ_cd IN ('SMCOV','SMPY','SCHRG','SCHRG','SMPY','SMDED','SCOIN','SCPAY')
),
clm_id AS (
SELECT DISTINCT csp.UNQ_CLM_ID
from (SELECT *
FROM UDWBASESECUREVIEW1. FINC_EXPN_CLM_CSP_SL_DTL
WHERE PS9_GL_CUST_NBR IN ('3113011','3113009','3113012','1108002','2228044','5322030','2111008','2228045','0169001','0169002')
AND DATA_TYP_CD ='CLPD'
AND ONST_OFFST_IND = 'A'
AND ORIG_SRC_SYS_CD ='CSP'
AND BEN_COV_TYP_CD ='M'
) csp

INNER JOIN UDWBASESECUREVIEW1.FINC_EXPN_TRANS_CSP_KEY_MAP fta 
    ON csp.UDW_FINC_TRANS_ID = fta.UDW_FINC_TRANS_ID
    AND fta.orig_src_sys_cd = 'CSP' 
    AND csp.ftp_cd = fta.ftp_cd

)
select DISTINCT
mce.SRC_CLM_ID
, mce.udw_med_clm_id
,mce.udw_adjd_mce_id
,srvc.ADJD_MED_CLM_SRVC_LN_NUM
,mce.proc_dt
,csp.TRANS_ID
,csp.ADJD_DT
,csp.ER_GRP
,csp.BIL_RECV_DT
,plsrv.PLSRV_CD
,plsrvdesc.PLSRV_DESC
,proc.PROC_CD
,procdesc.PROC_DESC
,csp.BIL_PROC_MOD_1_CD
,csp.BK_MO
,csp.CO_CD
,csp.SRC_CUST_CONTR_ID
,csp.PRDCT_CD
,csp.TRANS_FST_SRVC_DT
,csp.FTP_CD
,csp.GL_AMT
,csp.PAY_ISS_DT
,csp.TRANS_LST_SRVC_DT
,csp.MBR_BTH_DT
,csp.MBR_FST_NM
,csp.MBR_LST_NM
,csp.AREA_NTWK_IND
,csp.PD_PROV_MPIN
,csp.PD_PROV_TIN
,csp.RGN_SEG_CD
,diag.DIAG_CD
,diag.ICD_VER_CD
,diagdesc.DIAG_DESC
,csp.SCHED_BK_DT
,csp.SRVC_PROV_FULL_NM
,csp.SRVC_PROV_IPA_NUM
,csp.SRVC_PROV_MPIN
,csp.SRVC_PROV_NPI
,csp.SBSCR_FULL_NM
,csp.SBSCR_ID
,csp.CLM_SYS_ENT_DT
,csp.SRVC_TRANS_TYP_CD
,csp.UNQ_CLM_ID
,csp.PAYMT_REF_ID
,csp.MBR_ID
,csp.PROV_MKT_SITE_NUM
,csp.SRC_PROV_TYP_CD
,srcprovtyp.SRC_PROV_TYP_DESC
,csp.SPCL_TYP_CD
,spcltyp.SPCL_TYP_FULL_DESC
,csp.CLM_LN_STS_CD
,csp.BTCH_DT
,csp.PHYSN_STS_CD
,csp.MEDCR_ELIG_CD
,mce.ADJD_BIL_TYP_CD
,biltyp.BIL_TYP_DESC
,csp.DATA_TYP_CD
,csp.INCUR_DT
,csp.SRC_LOB_ID
,csp.ONST_OFFST_IND
,csp.PS9_GL_ACCT_NBR
,csp.PS9_GL_CUST_NBR
,csp.PS9_GL_DEPT_CD
,csp.PS9_GL_LE_CD
,csp.PS9_GL_LOC_NBR
,csp.PS9_GL_PRDCT_CD
,csp.PS9_GL_SEG_CD
,csp.CLM_SEQ_NBR
,csp.CLM_SRVC_TYP_CD
,csp.FTS_UPDT_TS
,csp.ORIG_SRC_SYS_CD
,csp.SRC_GL_IDB_DT
,csp.SRC_SYS_CD
,csp.UDW_MBR_COV_ID
,csp.UDW_PAY_ID
,csp.udw_finc_trans_id
, SMCOV.src_amt AS Allowed_Amt
,SMPY.src_amt AS Paid_Amount
,SCHRG.src_amt AS Billed_Amount
,SCHRG.src_amt AS Claim_Amount
,SMPY.src_amt AS Net_Paid_Amount
,SMDED.src_amt AS Deducted_Amount
,SCOIN.src_amt AS Coins_Amount
,SCPAY.src_amt AS Copay_Deductible_Amount
,rvnu.UNIT_CNT AS Servicing_Units
,rvnu.RVNU_CD AS REVENUE_CD
,mce.clm_evnt_grp_cd
,mce.clm_ben_typ_cd
,mce.clm_inst_or_prof_cd
,mce.mbr_pty_id

FROM  ( select * from UDWBASESECUREVIEW1. ADJD_MCE m
            WHERE   m.SRC_CLM_ID in (select distinct UNQ_CLM_ID from clm_id )
    AND  m.ORIG_SRC_SYS_CD='APF' 
    AND  m.SRC_SYS_CD ='UCR'
    AND m.SRC_ROW_EXPIR_DT    = '9999-12-31'  
    AND m.SRC_ROW_STS_CD = 'A'  
    AND m.clm_ben_typ_cd ='M'
    ) mce

    INNER JOIN UDWBASESECUREVIEW1.ADJD_MCE_CURR c 
    on mce.UDW_MED_CLM_ID = c.UDW_MED_CLM_ID 
    AND mce.UDW_ADJD_MCE_ID = c.UDW_ADJD_MCE_ID 
    AND c.CURR_VER_CD ='YC' 
    AND c.ROW_EXPIR_DT   = '9999-12-31'  
    AND c.ORIG_SRC_SYS_CD='APF' 
    AND  c.SRC_SYS_CD ='UCR'

INNER JOIN UDWBASESECUREVIEW1.ADJD_MCE_SRVC srvc
ON mce.udw_med_clm_id = srvc.udw_med_clm_id
AND mce.udw_adjd_mce_id = srvc.udw_adjd_mce_id
AND mce.proc_dt = srvc.proc_dt
AND srvc.orig_src_sys_cd = 'APF'
AND srvc.SRC_ROW_EXPIR_DT = '9999-12-31'
AND srvc.SRC_ROW_STS_CD = 'A'

INNER JOIN UDWBASESECUREVIEW1.FINC_EXPN_TRANS_CSP_KEY_MAP fta
ON mce.SRC_CLM_ID = fta.UNQ_CLM_ID
AND srvc.ADJD_MED_CLM_SRVC_LN_NUM = cast(fta.CLM_SEQ_NBR as integer)
AND fta.orig_src_sys_cd = 'CSP'
AND fta.SRC_SYS_CD = 'CSP'
AND fta.ONST_OFFST_IND = 'A'
AND fta.DATA_TYP_CD ='CLPD'

INNER JOIN (select * from UDWBASESECUREVIEW1. FINC_EXPN_CLM_CSP_SL_DTL c
                 WHERE c.PS9_GL_CUST_NBR IN ('3113011','3113009','3113012','1108002','2228044','5322030','2111008','2228045','0169001','0169002')
                    AND  c.DATA_TYP_CD ='CLPD'
                    AND  c.ORIG_SRC_SYS_CD ='CSP'
                    AND  c.SRC_SYS_CD ='CSP'
                    AND  c.ONST_OFFST_IND = 'A'
                    AND c.BEN_COV_TYP_CD ='M'
                    --AND  c.UNQ_CLM_ID IN ('20I399948900','20C644340901')
                     ) csp
    ON csp.UDW_FINC_TRANS_ID = fta.UDW_FINC_TRANS_ID
    AND  fta.ftp_cd = csp.ftp_cd

LEFT JOIN amt SMCOV
ON SMCOV.udw_med_clm_id = srvc.udw_med_clm_id
AND srvc.udw_adjd_mce_id = SMCOV.udw_adjd_mce_id
AND srvc.ADJD_MED_CLM_SRVC_LN_NUM = SMCOV.ADJD_MED_CLM_SRVC_LN_NUM
AND srvc.PROC_DT = SMCOV.proc_dt
AND SMCOV.orig_src_sys_cd = 'APF'
AND SMCOV.amt_typ_cd = 'SMCOV'

LEFT JOIN amt SMPY
ON SMPY.udw_med_clm_id = srvc.udw_med_clm_id
AND srvc.udw_adjd_mce_id = SMPY.udw_adjd_mce_id
AND srvc.ADJD_MED_CLM_SRVC_LN_NUM = SMPY.ADJD_MED_CLM_SRVC_LN_NUM
AND srvc.PROC_DT = SMPY.proc_dt
AND SMPY.orig_src_sys_cd = 'APF'
AND SMPY.amt_typ_cd = 'SMPY'

LEFT JOIN amt SCHRG
ON SCHRG.udw_med_clm_id = srvc.udw_med_clm_id
AND srvc.udw_adjd_mce_id = SCHRG.udw_adjd_mce_id
AND srvc.ADJD_MED_CLM_SRVC_LN_NUM = SCHRG.ADJD_MED_CLM_SRVC_LN_NUM
AND srvc.PROC_DT = SCHRG.proc_dt
AND SCHRG.orig_src_sys_cd = 'APF'
AND SCHRG.amt_typ_cd = 'SCHRG'

LEFT JOIN amt SMDED
ON srvc.udw_med_clm_id = SMDED.udw_med_clm_id
AND srvc.udw_adjd_mce_id = SMDED.udw_adjd_mce_id
AND srvc.ADJD_MED_CLM_SRVC_LN_NUM = SMDED.ADJD_MED_CLM_SRVC_LN_NUM
AND srvc.PROC_DT = SMDED.proc_dt
AND SMDED.orig_src_sys_cd = 'APF'
AND SMDED.amt_typ_cd = 'SMDED'

LEFT JOIN amt SCOIN
ON srvc.udw_med_clm_id =SCOIN.udw_med_clm_id
AND srvc.udw_adjd_mce_id = SCOIN.udw_adjd_mce_id
AND srvc.ADJD_MED_CLM_SRVC_LN_NUM = SCOIN.ADJD_MED_CLM_SRVC_LN_NUM
AND srvc.PROC_DT = SCOIN.proc_dt
AND SCOIN.orig_src_sys_cd = 'APF'
AND SCOIN.amt_typ_cd = 'SCOIN'

LEFT JOIN amt SCPAY
ON srvc.udw_med_clm_id = SCPAY.udw_med_clm_id
AND srvc.udw_adjd_mce_id = SCPAY.udw_adjd_mce_id
AND srvc.ADJD_MED_CLM_SRVC_LN_NUM = SCPAY.ADJD_MED_CLM_SRVC_LN_NUM
AND srvc.PROC_DT = SCPAY.proc_dt
AND SCPAY.orig_src_sys_cd = 'APF'
AND SCPAY.amt_typ_cd = 'SCPAY'

LEFT JOIN UDWBASESECUREVIEW1.BIL_TYP biltyp
ON mce.ADJD_BIL_TYP_CD = biltyp.BIL_TYP_CD

LEFT JOIN UDWBASESECUREVIEW1.SRC_PROV_TYP src_prov_typ
ON csp.SRC_PROV_TYP_CD = src_prov_typ.SRC_PROV_TYP_CD
AND src_prov_typ.ORIG_SRC_SYS_CD ='CSP'

LEFT JOIN UDWBASESECUREVIEW1.SPCL_TYP spcl_typ
ON csp.SPCL_TYP_CD = spcl_typ.SPCL_TYP_CD
AND spcl_typ.ORIG_SRC_SYS_CD ='CSP'
AND spcl_typ.SRC_SYS_CD ='CSP'

LEFT JOIN UDWBASESECUREVIEW1.ADJD_MCE_SRVC_PLSRV plsrv
ON srvc.udw_med_clm_id =plsrv.udw_med_clm_id
AND srvc.udw_adjd_mce_id = plsrv.udw_adjd_mce_id
AND srvc.ADJD_MED_CLM_SRVC_LN_NUM = plsrv.ADJD_MED_CLM_SRVC_LN_NUM
AND srvc.PROC_DT = plsrv.proc_dt
AND plsrv.orig_src_sys_cd = 'APF'
AND plsrv.SRC_ROW_EXPIR_DT = '9999-12-31'
AND plsrv.SRC_ROW_STS_CD = 'A'
AND plsrv.PLSRV_ROLE_CD ='ADJD'

LEFT JOIN UDWBASESECUREVIEW1.PLSRV plsrvdesc
ON plsrv.PLSRV_CD = plsrvdesc.PLSRV_CD

LEFT JOIN UDWBASESECUREVIEW1.ADJD_MCE_SRVC_PROC proc
ON srvc.udw_med_clm_id = proc.udw_med_clm_id
AND srvc.udw_adjd_mce_id = proc.udw_adjd_mce_id
AND srvc.ADJD_MED_CLM_SRVC_LN_NUM = proc.ADJD_MED_CLM_SRVC_LN_NUM
AND srvc.PROC_DT = proc.proc_dt
AND proc.orig_src_sys_cd = 'APF'
AND proc.SRC_ROW_EXPIR_DT = '9999-12-31'
AND proc.SRC_ROW_STS_CD = 'A'

LEFT JOIN UDWBASESECUREVIEW1.PROC procdesc
ON proc.proc_cd = procdesc.proc_cd

LEFT JOIN UDWBASESECUREVIEW1.SRC_PROV_TYP srcprovtyp
ON csp.SRC_PROV_TYP_CD = srcprovtyp.SRC_PROV_TYP_CD
AND srcprovtyp.ORIG_SRC_SYS_CD ='CSP'

LEFT JOIN UDWBASESECUREVIEW1.SPCL_TYP spcltyp
ON csp.SPCL_TYP_CD = spcltyp.SPCL_TYP_CD
AND spcltyp.ORIG_SRC_SYS_CD ='CSP'
AND spcltyp.SRC_SYS_CD ='CSP'

LEFT JOIN UDWBASESECUREVIEW1.ADJD_MCE_SRVC_INST rvnu
ON srvc.udw_med_clm_id = rvnu.udw_med_clm_id
AND srvc.udw_adjd_mce_id = rvnu.udw_adjd_mce_id
AND srvc.PROC_DT = rvnu.proc_dt
AND srvc.ADJD_MED_CLM_SRVC_LN_NUM = rvnu.ADJD_MED_CLM_SRVC_LN_NUM
AND rvnu.orig_src_sys_cd = 'APF'
AND rvnu.SRC_ROW_EXPIR_DT = '9999-12-31'
AND rvnu.SRC_ROW_STS_CD = 'A'

LEFT JOIN UDWBASESECUREVIEW1.ADJD_MCE_DIAG diag
        ON     srvc.udw_med_clm_id  = diag.udw_med_clm_id 
        AND srvc.udw_adjd_mce_id = diag.udw_adjd_mce_id
        AND diag.DIAG_ROLE_TYP_CD  = 'BK'

LEFT JOIN UDWBASESECUREVIEW1.DIAG diagdesc
ON diag.DIAG_CD = diagdesc.DIAG_CD
AND diag.ICD_VER_CD = diagdesc.ICD_VER_CD

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,612 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Monalv-MSFT 5,901 Reputation points
    2021-01-18T02:36:26.207+00:00

    Hi @Gopinath Desai ,

    Please run your CTE sql query in SSMS to check which line has errors.

    Best Regards,
    Mona

    ----------

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.