Archives of the TeradataForum

Message Posted: Tue, 18 Mar 2008 @ 17:52:46 GMT

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

<-- Anonymously Posted: Tue, 18 Mar 2008 12:10 -->

I have a question regarding Ml-PPI Aggregate Join Indexes on Version R12.

In the 1^st example below, the 2^nd level partition logic casts a numeric value (in format of YYYYMM) as CHAR and then a date. The 2^nd example derives the date via date arithmetic.

The problem is the 1^st example has left the ML-PPI AJI and all associated objects in a corrupt state. I can�t drop the AJI, select from the base table, alter partition ranges, etc. *The error I consistently get is: 3706 Syntax error: Expected Character Data Type*

The 2^nd example was created on a different table with the same structure and everything appears to be fine.

I realize this version is relatively new, but has anyone come across this?

Original:

     CREATE JOIN INDEX . ,NO FALLBACK ,
                                      CHECKSUM = DEFAULT AS

     SELECT CAUS_LS_ID,
                     PIT_DT,
                     FILE_STS_ID,
                     CLM_HNDL_ID,
                     NOL_OFC_ID,
                     CNTL_AFO_ID,
                     LS_DESG_ID,
                     POL_TYP_ID,
                     BEN_ST_ID,
                     ACC_ST_ID,
                     DT.YEAR_NUM,
                     DT.MONTH_DIM_ID,
                     DT.MONTH_NM,
                     COUNT(*)(FLOAT, NAMED CountStar ),
                     SUM(CR_AMT )(FLOAT, NAMED Credit ),
                     SUM(CLM_EST_AMT )(FLOAT, NAMED Total_Estimate ),
                     SUM(ALAE_AMT )(FLOAT, NAMED alae_Amt ),
                     SUM(CLM_CNT )(FLOAT, NAMED Claim_Count )

        FROM TBL1  CLM_FCT
           JOIN TBL2  DT
              ON CLM_FCT.PIT_DT = DT.DATE_ID

     WHERE CLM_FCT.EXCL_CLM_IND = 'N'

     GROUP BY CAUS_LS_ID, PIT_DT,FILE_STS_ID, CLM_HNDL_ID,NOL_OFC_ID,
     CNTL_AFO_ID, LS_DESG_ID, POL_TYP_ID,  BEN_ST_ID, ACC_ST_ID, DT.YEAR_NUM,
     DT.MONTH_DIM_ID, DT.MONTH_NM

     PRIMARY INDEX
        ( POL_TYP_ID, CNTL_AFO_ID, NOL_OFC_ID, CAUS_LS_ID,  CLM_HNDL_ID,
     ACC_ST_ID, BEN_ST_ID,  FILE_STS_id, PIT_DT )


     *PARTITION BY (RANGE_N( YEAR_NUM BETWEEN 2005 AND 2010 EACH 1) ,*
     *                                RANGE_N(CAST(CAST(MONTH_DIM_ID AS
     CHAR(6)) AS DATE FORMAT 'YYYYMM')*
     *                                                   BETWEEN DATE
     '2005-01-01' AND DATE '2010-12-31' EACH INTERVAL '1' MONTH) );*



     CREATE JOIN INDEX . ,NO FALLBACK ,
                                      CHECKSUM = DEFAULT AS

     SELECT CAUS_LS_ID,
                     PIT_DT,
                     FILE_STS_ID,
                     CLM_HNDL_ID,
                     NOL_OFC_ID,
                     CNTL_AFO_ID,
                     LS_DESG_ID,
                     POL_TYP_ID,
                     BEN_ST_ID,
                     ACC_ST_ID,
                     DT.YEAR_NUM,
                     DT.MONTH_DIM_ID,
                     DT.MONTH_NM,
                     COUNT(*)(FLOAT, NAMED CountStar ),
                     SUM(CR_AMT )(FLOAT, NAMED Credit ),
                     SUM(CLM_EST_AMT )(FLOAT, NAMED Total_Estimate ),
                     SUM(ALAE_AMT )(FLOAT, NAMED alae_Amt ),
                     SUM(CLM_CNT )(FLOAT, NAMED Claim_Count )

        FROM TBL1  CLM_FCT
           JOIN TBL2  DT
              ON CLM_FCT.PIT_DT = DT.DATE_ID

     WHERE CLM_FCT.EXCL_CLM_IND = 'N'

       GROUP BY CAUS_LS_ID, PIT_DT,FILE_STS_ID, CLM_HNDL_ID,NOL_OFC_ID,
     CNTL_AFO_ID, LS_DESG_ID, POL_TYP_ID,  BEN_ST_ID, ACC_ST_ID, DT.YEAR_NUM,
     DT.MONTH_DIM_ID, DT.MONTH_NM

     PRIMARY INDEX
        ( POL_TYP_ID, CNTL_AFO_ID, NOL_OFC_ID, CAUS_LS_ID,  CLM_HNDL_ID,
     ACC_ST_ID, BEN_ST_ID,  FILE_STS_id, PIT_DT )


     *PARTITION BY ( *
     *           RANGE_N (YEAR_NUM BETWEEN 2005 AND 2010 EACH 1) ,*
     *           RANGE_N (CAST((((MONTH_DIM_ID / 100) - 1900) * 10000 +
     ((MONTH_DIM_ID MOD 100) * 100) + 1) AS  DATE) *
     *                             BETWEEN DATE '2005-01-01' AND DATE
     '2010-12-31' EACH INTERVAL '1' MONTH ) );*

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