Archives of the TeradataForum

Message Posted: Wed, 10 Mar 2010 @ 10:30:59 GMT

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

Hello,

I am facing the same error while executing the below Join Index as these are the Materialized View in Oracle and migrating to Teradata -

5464: Error in Join Index DDL, Only Column, Extract, SUM or COUNT expression with aliases are allowed in select list

     CREATE JOIN INDEX  WOFDWRPT.V_INT_APPL_ORGL_NTFY_DATE_MV2_JI
     AS

     SELECT D.DY DY, D.DW_CRRT_FL CURRENT_FLAG, D.MTH CURRENT_MTH,
            (CAST(D.DY AS DATE)
                  - EXTRACT(day FROM (CAST( D.DY AS DATE)))+1) CURRENT_MTH_BEG_DT,
            ADD_MONTHS ((CAST(D.DY AS DATE)
                  - EXTRACT(day FROM (CAST( D.DY AS DATE)))+1), 1) - 1 CURRENT_MTH_END_DT,
            D.WOFC_MTH_END_FLG CURRENT_MTH_END_FLG,
            D.WOFC_QTR_END_FLG CURRENT_QTR_END_FL, D.YR CURRENT_YEAR,
            ((D.YR||'01/01')(DATE)) CURRENT_YEAR_BEG_DT,
            ((D.YR||'01/01')(DATE))CURRENT_YEAR_END_DT,
            D.WOFC_YR_END_FLG CURRENT_YEAR_END_FL, D.HDAY_FLG HOLIDAY_FLG,
            CAST(ADD_MONTHS (CASE WOFC_MTH_END_FLG
                                    WHEN 'Y'
                                       THEN WOFCO_FRST_DY_MTH
                                    ELSE WOFCO_BEGIN_MTH_DT
                                 END,
                                 -1)AS CHAR(7)) PREV_REP_MTH,
          ADD_MONTHS (CASE WOFC_MTH_END_FLG
                           WHEN 'Y'
                              THEN WOFCO_FRST_DY_MTH
                           ELSE WOFCO_BEGIN_MTH_DT
                        END,
                        -1
                       ) PREV_REP_MTH_BEG_DT,
            ADD_MONTHS (CASE WOFC_MTH_END_FLG
                           WHEN 'Y'
                              THEN D.DY
                           ELSE D.WOFCO_MTH_END_DT
                        END,
                        -1
                       ) PREV_REP_MTH_END_DT,
            D.PREV_MTH PRIOR_MTH,
            CASE WOFC_MTH_END_FLG
               WHEN 'Y'
                  THEN MTH
               ELSE PREV_MTH
            END REP_MTH,
            CASE WOFC_MTH_END_FLG
               WHEN 'Y'
                  THEN WOFCO_FRST_DY_MTH
               ELSE WOFCO_BEGIN_MTH_DT
            END REP_MTH_BEG_DT,
            CASE WOFC_MTH_END_FLG
               WHEN 'Y'
                  THEN D.DY
               ELSE D.WOFCO_MTH_END_DT
            END REP_MTH_END_DT,
            CASE WOFC_MTH_END_FLG
               WHEN 'Y'
                  THEN ADD_MONTHS (D.WOFCO_FRST_DY_MTH,
                                   12
                                  )
               ELSE ADD_MONTHS (D.WOFCO_BEGIN_MTH_DT, 12)
            END REP_MTH_NEXT_YEAR_BEG_DT,
            CASE WOFC_MTH_END_FLG
               WHEN 'Y'
                  THEN ADD_MONTHS (D.DY, 12)
               ELSE D.WOFCO_MTH_NXT_YR_DT
            END REP_MTH_NEXT_YEAR_END_DT,
            CASE WOFC_MTH_END_FLG
               WHEN 'Y'
                  THEN D.YR
               ELSE SUBSTR (PREV_MTH, 1, 4)
            END REP_YEAR,
            CASE WOFC_MTH_END_FLG
               WHEN 'Y'
                  THEN ((D.YR||'01/01')(DATE))
               ELSE  ((SUBSTR (PREV_MTH, 1, 4) || '/01/01')(DATE))
            END REP_YEAR_BEG_DT,
            CASE WOFC_MTH_END_FLG
               WHEN 'Y'
                  THEN ((D.YR||'01/01')(DATE))
               ELSE  ((SUBSTR (PREV_MTH, 1, 4) || '/01/01')(DATE))
            END REP_YEAR_END_DT
       FROM WOFDWRPT.D_DT D
      WHERE D.DY <> DATE '9999-12-31';

Thanks,

Syed

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