Archives of the TeradataForum

Message Posted: Thu, 03 Nov 2011 @ 13:15:43 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

<-- Anonymously Posted: Wednesday, November 02, 2011 14:27 -->

Hi Folks,

I am getting a 3706 error while executing the below sql. Can anyone please help where I might be going wrong. Thanks much.

Error: 3706 Syntax error: expected something between '(' and the 'current_date' keyword.

     SELECT
     HSP_TRANSACTIONS.TX_ID,
     MAX(CASE WHEN CLR_DEP.DEPT_ABBREVIATION IS NOT NULL THEN
     SUBSTR(CLR_DEP.DEPT_ABBREVIATION,1,3)
     ELSE SUBSTR(CLR_DEP_ADM.DEPT_ABBREVIATION,1,3) END ) AS Facility,
     MAX(CLR_EAP.PROC_CODE) AS EAP_#,
     MAX(CASE WHEN CLR_DEP.DEPT_ABBREVIATION IS NOT NULL THEN
     SUBSTR(CLR_DEP.DEPT_ABBREVIATION,4,3)
     ELSE SUBSTR(CLR_DEP_ADM.DEPT_ABBREVIATION,4,3) END ) AS Dept FROM PRODB.HSP_TRANSACTIONS
     HSP_TRANSACTIONS LEFT OUTER JOIN PRODB.HSP_ACCOUNT HSP_ACCOUNT ON
     HSP_TRANSACTIONS.HSP_ACCOUNT_ID = HSP_ACCOUNT.HSP_ACCOUNT_ID LEFT OUTER JOIN
     kpbinc_pbhb.hsp_account_lob HSP_ACCOUNT_LOB ON HSP_ACCOUNT.HSP_ACCOUNT_ID =
     HSP_ACCOUNT_LOB.HSP_ACCOUNT_ID LEFT OUTER JOIN KPBINC_PBHB.LN_OF_BSNS LN_OF_BSNS ON
     HSP_ACCOUNT_LOB.LOB_ID=LN_OF_BSNS.LOB_ID
     LEFT OUTER JOIN PRODB.CLR_EAP CLR_EAP
     ON HSP_TRANSACTIONS.PROC_ID = CLR_EAP.PROC_ID LEFT OUTER JOIN PRODB.CLR_LOC CLR_LOC ON
     HSP_ACCOUNT.LOC_ID = CLR_LOC.LOC_ID  LEFT OUTER JOIN PRODB.ZC_LOC_RPT_GRP_7
     ZC_LOC_RPT_GRP_7 ON CLR_LOC.RPT_GRP_SEVEN = ZC_LOC_RPT_GRP_7.RPT_GRP_SEVEN LEFT OUTER
     JOIN PRODB.CLR_DEP CLR_DEP ON HSP_TRANSACTIONS.DEPARTMENT = CLR_DEP.DEPARTMENT_ID LEFT
     OUTER JOIN PRODB.PAT_ENC_HSP PAT_ENC_HSP ON PAT_ENC_HSP.HSP_ACCOUNT_ID =
     HSP_ACCOUNT.HSP_ACCOUNT_ID LEFT OUTER JOIN PRODB.CLR_DEP CLR_DEP_adm ON
     PAT_ENC_HSP.DEPARTMENT_ID = CLR_DEP_adm.DEPARTMENT_ID WHERE HSP_TRANSACTIONS.TX_POST_DATE
     >= case when 'Last Full Month' = 'Last Full Month' then add_months(current_date, -1)
     - dayofmonth(current_date) +1
     when 'Last Full Month' = 'Last Full Week' Then current_date -
     dayofweek(current_date) -6
     else CAST('2011-10-01' AS DATE FORMAT 'YYYY-MM-DD') end  AND
     HSP_TRANSACTIONS.TX_TYPE_HA_C = 1 AND CLR_EAP.PROC_CODE NOT IN ( '4007', '4008', '4009')
     GROUP BY 1

	<Prev	Next>		<<First	<Prev	Next>	Last>>