![](//teradataforum.com/imgs/ptdfw1.gif) |
![](//teradataforum.com/imgs/ptdfw2.gif) |
Archives of the TeradataForum
Message Posted: Tue, 09 Mar 2010 @ 16:21:10 GMT
Subj: | | Join Index: Column, Extract, SUM or COUNT expressions |
|
From: | | Ahmad, Syed |
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
| |