Aggregate Join Index on Complex SQL statements

Database
Fan

Aggregate Join Index on Complex SQL statements

Hi, I am trying to create an Aggregate Join Index with below sql..

CREATE JOIN INDEX PART_SALES_CUBE_AJI AS

SELECT 

COUNT(*)(FLOAT, NAMED CountStar ),

a12.ANLST_ID  ANLST_ID,

        a11.BKLG_MSR_IND  BKLG_MSR_IND,

        a12.BUYER_ID  BUYER_NM,

        a12.BUYER_ID  BUYER_NM1,

        a11.CNSMPN_FCLTY_CD  CNSMPN_FCLTY_CD,

        CASE 

WHEN a11.SSC_CD IN ('BTS','BTP') AND a11.FGA_ID IS NOT NULL  THEN 'FGA EMBEDDED'

WHEN a11.EMBDD_FLG = 'Y' THEN 'EMBEDDED'

WHEN a11.EMBDD_FLG = 'N' THEN 'NOT EMBEDDED'

ELSE NULL END  CustCol_3,

        a11.EMBDD_FLG  EMBDD_FLG,

        a11.SSC_CD  SSC_CD,

        a13.FULFLT_RGN_DESC  FULFLT_RGN_DESC,

        CASE WHEN (a15.SUB_RGN_DESC= 'Inter-Company') THEN 'United States and Canada' ELSE a15.SUB_RGN_DESC END  SUB_RGN_DESC,

        a12.ISS_CD  ISS_CD,

        a11.SYS_PROD_DEPENDENCY_CD  OPS_SALE_TYPE,

        a12.ITM_DESC  ITM_DESC,

        a11.PART_ITM_NBR  PART_ITM_NBR,

        a17.FISC_WEEK_VAL  FISC_MTH_VAL,

        a16.FMLY_PARNT_PROD_TYPE_CD  FMLY_PARNT_PROD_TYPE_DESC,

        a11.SYS_FLG  SYS_FLG,

        a11.CCN  CCN,

        CASE WHEN a11.ARB_FLG = 'Y' THEN 'ARB' ELSE a11.DERIVED_SLS_CHNL_VAL END  DERIVED_SLS_CHNL_VAL,

        SUM(a11.PART_ITM_QTY)  (FLOAT, NAMED PrtItmQty )

FROM   

ACTL_SALE_PART_SMRY_VW a11,

MFG_PART_ATTR a12,

FULFLT_RGN a13,

CHNL_HIER a14,

PHYS_GEO_HIER a15,

COMB_PROD_HIER a16,

CORP_CLDR a17

WHERE (a11.CCN = a12.CCN)

AND (a11.BUILD_FCLTY_CD = a13.FCLTY_CD AND a11.INTER_CO_BU_ID = a13.INTER_CO_BU_ID)

AND (a11.INTER_CO_BU_ID = a14.BU_ID AND a11.INTER_CO_LCL_CHNL_CD = a14.LCL_CHNL_CODE)

AND (a14.RPTG_BU_ID = a15.BU_ID)

AND (a11.BASE_SYS_CD = a16.COMB_HIER_CD)

AND (a11.PART_SLS_DT = a17.CLDR_DATE)

GROUP BY 

a12.ANLST_ID,

    a11.BKLG_MSR_IND,

    a12.BUYER_ID,

    a12.BUYER_ID,

    a11.CNSMPN_FCLTY_CD,

    CASE 

WHEN a11.SSC_CD IN ('BTS','BTP') AND a11.FGA_ID IS NOT NULL  THEN 'FGA EMBEDDED'

WHEN a11.EMBDD_FLG = 'Y' THEN 'EMBEDDED'

WHEN a11.EMBDD_FLG = 'N' THEN 'NOT EMBEDDED'

ELSE NULL END,

    a11.EMBDD_FLG,

    a11.SSC_CD,

    a13.FULFLT_RGN_DESC,

    CASE WHEN (a15.SUB_RGN_DESC= 'Inter-Company') THEN 'United States and Canada' ELSE a15.SUB_RGN_DESC END,

    a12.ISS_CD,

    a11.SYS_PROD_DEPENDENCY_CD,

    a12.ITM_DESC,

    a11.PART_ITM_NBR,

    a17.FISC_WEEK_VAL,

    a16.FMLY_PARNT_PROD_TYPE_CD,

    a11.SYS_FLG,

    a11.CCN,

    CASE WHEN a11.ARB_FLG = 'Y' THEN 'ARB' ELSE a11.DERIVED_SLS_CHNL_VAL END;

I am getting error saying "Executed as Single statement.  Failed [5464 : HY000] Error in Join Index DDL, Only satisfiable single-table conditions and equality join conditions are allowed in the WHERE clause. 

Elapsed time = 00:00:00.031 

STATEMENT 1: CREATE  failed."

I am new to Teradata environment. Please help me to identify the cause of this error and help create AJI for above SQL...

Tags (1)
4 REPLIES
Teradata Employee

Re: Aggregate Join Index on Complex SQL statements

no primary index?

Fan

Re: Aggregate Join Index on Complex SQL statements

This is kind of aggrigate table coming from joining 1 fact and 6 dimention table.. so I have not added any primary key. is this incorrect?

Fan

Re: Aggregate Join Index on Complex SQL statements

Hi,

Can you please help me to identify the problem in above statement.

Regards,

Rajesh Suyal

Enthusiast

Re: Aggregate Join Index on Complex SQL statements

Hi Raj,

After GROUP BY there is few CASE statements. Is it possible to include in the SELECT clause and give a try?