Archives of the TeradataForum

Message Posted: Fri, 09 Oct 2009 @ 14:18:10 GMT

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

The below mentioned Query used to work fine in Teradata Queryman V7.1 but when It was updated to V13, it started givng an error mentioned below:

SELECT Failed. [3706] Syntax error: expected something between the 'WHEN' keyword and the 'MOD' keyword. Please someone help..

     /*HK_FEATURES QUERY:*/


     SELECT
        DW_CAL_DT.PRD_ID,
        DW_COUNTRIES_AE_SLR_INFO.CNTRY_DESC,
        DW_SITES_AE_ITEMS.SITE_NAME,
        SUM(CASE WHEN DW_LSTG_ITEM.SUCCESS_YN='Y' THEN 1 ELSE 0 END) SUCCESS_LISTINGS,
        CAST(COUNT(DW_LSTG_ITEM.ITEM_ID) AS DECIMAL(18,2)) LISTINGS,
        DW_LSTG_GLRY_TYPE_LKP.GLRY_TYPE_DESC,
        CASE WHEN DW_LSTG_ITEM.ITEM_SITE_ID <> 100
        AND
        ((
           (DW_LSTG_ITEM_COLD.THEME_ID) <= 0
           OR
           (DW_LSTG_ITEM_COLD.THEME_ID) = ((DW_LSTG_ITEM.ITEM_SITE_ID) * 100 + 10))
        AND
        (
        (DW_LSTG_ITEM_COLD.LAYOUT_ID) <= 0
        OR
        (DW_LSTG_ITEM_COLD.LAYOUT_ID) =
        ((DW_LSTG_ITEM.ITEM_SITE_ID) * 100000 + 10000)
        )
        )
        THEN 'N'
        WHEN DW_LSTG_ITEM.ITEM_SITE_ID = 100
        AND
        ((
        (DW_LSTG_ITEM_COLD.THEME_ID)= 10
        OR
        (DW_LSTG_ITEM_COLD.THEME_ID) <= 0
        )
        AND
        (
        (DW_LSTG_ITEM_COLD.LAYOUT_ID)= 10000
        OR
        (DW_LSTG_ITEM_COLD.LAYOUT_ID)<= 0
        ))
        THEN 'N'
        ELSE 'Y' END,
        SUM(CASE WHEN DW_LSTG_ITEM_REV.BOLD_FEE_USD = 0 THEN 0 ELSE 1 END) BOLD_FEE,
        CASE WHEN DW_LSTG_ITEM.BIN_PRICE_USD > 0 THEN 'Y' ELSE 'N' END  BIN,
        SUM(CASE WHEN DW_LSTG_ITEM_REV.SCHEDULE_FEE_USD = 0 THEN 0 ELSE 1 END) SCHEDULE_FEE,
        CASE WHEN MOD((((CAST(DW_LSTG_ITEM_COLD.FLAGS2  AS INTEGER))
           * (CASE WHEN (CAST(DW_LSTG_ITEM_COLD.FLAGS2 AS INTEGER))<0 THEN -1 ELSE 1
           END)) - (CASE WHEN (CAST(DW_LSTG_ITEM_COLD.FLAGS2  AS INTEGER))<0 THEN 1 ELSE 0 END))
           /131072, 2) = (CASE WHEN (CAST(DW_LSTG_ITEM_COLD.FLAGS2 AS INTEGER))<0 THEN 0 ELSE 1 END)
           THEN 'Y' ELSE 'N' END COLD_ITEM,
        SUM(CASE WHEN DW_LSTG_ITEM_REV.HILIGHT_FEATD_FEE_USD = 0 THEN 0 ELSE 1 END) HILIGHT_FEATD_FEE,
        DW_LSTG_ITEM.AUCT_DURTN_DAYS,
        CASE WHEN DW_LSTG_ITEM.LEAF_CATEG_ID2 <= 0 THEN 'N'  ELSE 'Y' END CATEG_ID2 ,
        CASE WHEN DW_LSTG_ITEM_REV.SUBTITLE_FEE_USD=0 THEN 'N' ELSE 'Y' END
     SUBTITLE_FEE FROM DW_CAL_DT INNER JOIN DW_LSTG_ITEM ON
     (DW_LSTG_ITEM.AUCT_END_DT=DW_CAL_DT.CAL_DT)
     INNER JOIN DW_LSTG_ITEM_COLD ON
     (DW_LSTG_ITEM.ITEM_ID=DW_LSTG_ITEM_COLD.ITEM_ID AND
     DW_LSTG_ITEM.AUCT_END_DT=DW_LSTG_ITEM_COLD.AUCT_END_DT)
     INNER JOIN DW_LSTG_GLRY_TYPE_LKP ON
     (DW_LSTG_ITEM_COLD.GLRY_TYPE_ID=DW_LSTG_GLRY_TYPE_LKP.GLRY_TYPE_ID)
     INNER JOIN DW_LSTG_ITEM_REV ON
     (DW_LSTG_ITEM.ITEM_ID=DW_LSTG_ITEM_REV.ITEM_ID AND
     DW_LSTG_ITEM.AUCT_END_DT=DW_LSTG_ITEM_REV.AUCT_END_DT)
     INNER JOIN DW_SITES  DW_SITES_AE_ITEMS ON
     (DW_LSTG_ITEM.ITEM_SITE_ID=DW_SITES_AE_ITEMS.SITE_ID)
     INNER JOIN DW_USERS  DW_USERS_AE_SLR_INFO ON
     (DW_USERS_AE_SLR_INFO.USER_ID=DW_LSTG_ITEM.SLR_ID)
     INNER JOIN DW_COUNTRIES  DW_COUNTRIES_AE_SLR_INFO ON
     (DW_LSTG_ITEM.SLR_CNTRY_ID=DW_COUNTRIES_AE_SLR_INFO.CNTRY_ID)
     WHERE
     DW_LSTG_ITEM.AUCT_TYPE_CODE NOT IN (12, 15) AND DW_LSTG_ITEM.AUCT_END_DT  >= '2009-06-01'
     AND  DW_LSTG_ITEM.SLR_CNTRY_ID  = 92
     AND   DW_LSTG_ITEM.ITEM_SITE_ID  = 201
     AND   DW_LSTG_ITEM.WACKO_YN  =  'N'
     AND  DW_USERS_AE_SLR_INFO.USER_STS_CODE  =  1 AND DW_CAL_DT.PRD_ID = '2009M09'
     GROUP BY
     1,2,3,6,7,9,11,13,14,15

Thanks and Regards

Sreeraj

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