Archives of the TeradataForum

Message Posted: Thu, 24 Oct 2002 @ 15:11:23 GMT

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

By way of example, here's one that I implemented earlier this morning

CREATE JOIN INDEX TESTCOE_T.ConEntryCost_AJI1 ,NO FALLBACK AS
SELECT COUNT(*)(FLOAT, NAMED CountStar),
TESTCoe_T.ConEntryCost.COE_SRKY_ID,
TESTCoe_T.ConEntryCost.CTM_PARTNER_CD,
SUM(TESTCoe_T.ConEntryCost.CEK_AM )(FLOAT, NAMED coe_p_cost_am )

 FROM TESTCoe_T.ConEntryCost

GROUP BY TESTCoe_T.ConEntryCost.COE_SRKY_ID,TESTCoe_T.ConEntryCost.CTM_PARTNER_CD
PRIMARY INDEX ( COE_SRKY_ID );

collect statistics TESTCOE_T.ConEntryCost_AJI1  column COE_SRKY_ID;
collect statistics TESTCOE_T.ConEntryCost_AJI1  column CTM_PARTNER_CD ;
collect statistics TESTCOE_T.ConEntryCost_AJI1  column coe_p_cost_am;

here's an associated query that makes use of it.... but beware! The optimizer reads the whole of the aggregate table before proceeding with my query. That might not be so relevant for you, but caution should be applied to these features in my (somewhat limited) experience ...

SELECT
   CE.CON_ID
   ,CE.COM_ID
   ,CE.BUL_ID_ORIG
   ,CE.CON_CREATE_DT
  ,CAST(CE.CON_CREATE_TM  AS CHAR(4)) as CON_CREATE_TM
  ,CAST(CE.COE_ID AS CHAR(3)) as COE_ID
   ,CE.COE_SRKY_ID
    ,RV.COE_COMPANY_AM
   ,RV.COE_CUSTOMER_AM
   ,RV.COE_HANDRATE_AM
   ,RV.COE_NET_REV_AM
   ,RV.COE_GFR_REV_AM
   ,RV.COE_NFR_REV_AM
 ,    CASE  WHEN RV.OPT_ID_2 is NULL then RV.OPT_ID_1
           WHEN RV.OPT_ID_3 is NULL then
             TRIM(RV.OPT_ID_1) || ' & ' ||
             TRIM(RV.OPT_ID_2)
           WHEN RV.OPT_ID_4 is NULL then
             TRIM(RV.OPT_ID_1) || ' & ' ||
             TRIM(RV.OPT_ID_2) || ' & ' ||
             TRIM(RV.OPT_ID_3)
           ELSE
             TRIM(RV.OPT_ID_1) || ' & ' ||
             TRIM(RV.OPT_ID_2) || ' & ' ||
             TRIM(RV.OPT_ID_3) || ' & ' ||
             TRIM(RV.OPT_ID_4)
     END as OPT_ID_STRING
 ,RV.OPT_ID_1
 ,RV.OPT_ID_2
 ,RV.OPT_ID_3
 ,RV.OPT_ID_4
 ,RV.CHD_SURLVL_ID_1
 ,RV.CHD_SURLVL_ID_2
 ,RV.CHD_SURLVL_ID_3
 ,RV.CHD_SURLVL_ID_4
 ,RV.CHD_SURLVL_ID_5
 ,RV.CHD_SURLVL_ID_6
 ,RV.CHD_SURLVL_ID_7
 ,RV.CHD_SURLVL_ID_8
 ,RV.CHD_SURLVL_ID_9
 ,RV.CHD_SURLVL_ID_10
 ,RV.CHD_HAZ_ZEROREV_IN
 ,RV.CHD_DI_ZEROREV_IN
 ,RV.CHD_HAZ_IN
 ,RV.CHD_DI_IN
 ,RV.OPT_HAZ_OPTION_IN
 ,RV.OPT_DI_OPTION_IN
,CASE WHEN myCEC.COE_SRKY_ID IS NULL THEN 'N' ELSE 'Y' END AS COE_COSTED_IN
,myCEC.CEK_AM_TOT
 FROM
 TESTCOE_V.CONENTRY_V01 CE INNER JOIN TESTCOE_V.CONENTRYREVENUE_V01 RV
 ON CE.COE_SRKY_ID = RV.COE_SRKY_ID
LEFT JOIN (SELECT SUM(CEK_AM ) as CEK_AM_TOT, COE_SRKY_ID FROM
TESTCOE_V.CONENTRYCOST_V01 GROUP BY COE_SRKY_ID) mycec
ON  CE.COE_SRKY_ID = myCEC.COE_SRKY_ID
;

R/ Dave Clough - TNT database designer

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