|
Archives of the TeradataForumMessage Posted: Fri, 09 Oct 2009 @ 14:18:10 GMT
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
| ||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||
Copyright 2016 - All Rights Reserved | ||||||||||||||||||||||||||||||||||||||||||||||||
Last Modified: 15 Jun 2023 | ||||||||||||||||||||||||||||||||||||||||||||||||