I can run below query in DB2, but I got the error message in Teradata. May anyone can help me to solve it?
UPDATE TMP1 FROM TEMP_PRD.T_CST_SUB_DAILY_INFO_TEMP TMP1 SET (TMP1.T_NO_OF_ACTIVE_MRT ) = ( SELECT COUNT (*) FROM TEMP_PRD.T_CST_SUB_DAILY_INFO_TEMP TMP2 WHERE TMP2.T_CUA_CUST_CODE = TMP1.T_CUA_CUST_CODE AND TMP2.T_SUB_STATUS IN ('A','O','S') );
UPDATE TMP1 FROM TEMP_PRD.T_CST_SUB_DAILY_INFO_TEMP TMP1 ,(SELECT T_CUA_CUST_CODE , COUNT (*) AS CC_COUNT FROM TEMP_PRD.T_CST_SUB_DAILY_INFO_TEMP TMP2 WHERE TMP2.T_SUB_STATUS IN ('A','O','S') GROUP BY 1) TMP2
SET T_NO_OF_ACTIVE_MRT = TMP2.CC_COUNT WHERE TMP2.T_CUA_CUST_CODE = TMP1.T_CUA_CUST_CODE;
(doesn't look very elegant though - there must be a better way).
If the table is large, there'll be an awful lot of redundant bytes being carried around - each row for a particular T_CUA_CUST_CODE will have the same count value. So if you have a million rows for a particular T_CUA_CUST_CODE with T_SUB_STATUS = 'A', you'll have T_NO_OF_ACTIVE_MRT set to 1000000 on each of them :-( Perhaps you should set up a different table that could describe the attributes of each individual Cust Code ?