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,525 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Monalv-MSFT 5,896 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.