Archives of the TeradataForum

Message Posted: Tue, 09 Mar 2010 @ 16:21:10 GMT

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

Hi,

I am running the below select query and it is running fine, once we want to create the join index it is giving the error that 5464: Error in Join Index DDL, Only Column, Extract, SUM or COUNT expression with aliases are allowed in select list

Any help would be definitely appreciated.

     CREATE JOIN INDEX WOFDWRPT.R_LKE_MV_JI
     AS

     SELECT A.PRMY_ABS_ID,
     C.ACCT_NBR,
     A.ACCT_NBR ,
     B.SALE_AM ,
     B.FNND_AM_CORE ,
     B.BOOK_DT_CORE ,
     B.CTRC_CASH_DWN_AM ,
     B.CUST_CORE_SK ,
     C.PRMY_ABS_ID ,
     B.DW_INSRT_TS ,
     D.DW_INSRT_TS ,
     B.DW_END_DT ,
     D.DW_END_DT ,
     D.SALE_AM ,
     D.FNND_AM_CORE,
     D.BOOK_DT_CORE,
     D.CTRC_CASH_DWN_AM,
     D.CUST_CORE_SK,
     (CASE WHEN E.CUST_FRST_NM = 'U/N' THEN NULL ELSE E.CUST_FRST_NM END || ' '|| CASE WHEN
     E.CUST_MDDL_NM = 'U/N' THEN NULL ELSE E.CUST_MDDL_NM END|| ' '||
     CASE WHEN E.CUST_LST_NM =        'U/N' THEN NULL ELSE E.CUST_LST_NM END),
     (CASE WHEN F.CUST_FRST_NM = 'U/N' THEN NULL ELSE F.CUST_FRST_NM END || ' '|| CASE WHEN
     F.CUST_MDDL_NM = 'U/N' THEN NULL ELSE F.CUST_MDDL_NM END|| ' '||
     CASE WHEN F.CUST_LST_NM =        'U/N' THEN NULL ELSE F.CUST_LST_NM END) ,
     (CASE WHEN D.LSE_CONVERSION_FLG = 0 THEN  'LTL' ELSE 'LTR' END), B.SALE_DT , A.TRMN_TYPE
     , C.CLNT_NBR_NK
       FROM WOFDWRPT.D_CTRC C, WOFDWRPT.F_PRFMC D, WOFDWRPT.D_CTRC A, WOFDWRPT.F_PRFMC B,
            WOFDWRPT.D_CUST E, WOFDWRPT.D_CUST F  WHERE A.CTRC_SK = B.CTRC_SK
        AND A.DW_CRRT_FL = 'Y'
        AND A.ACCT_NBR = C.ORGL_ACCT_NBR
        AND C.DW_CRRT_FL = 'Y'
        AND C.CTRC_SK = D.CTRC_SK
        AND E.CUST_D_SK = B.CUST_D_CORE_SK
        AND F.CUST_D_SK = D.CUST_D_CORE_SK
        AND D.LSE_CONVERSION_FLG IN (0, 1);

Thanks & Regards,

Syed Rehan Ahmad | Technical Consultant Teradata Corporation

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