Error in Merge Statement

Database
Fan

Error in Merge Statement

Hi Everyone,

I have created below merge statement in Teradata, but getting error while trying to execute this script.

MERGE INTO  AGG_PART_SALES_TEST_CUBE AS Target_tbl

      USING (SELECT 

a12.ANLST_ID  ANLST_ID,  

        a11.BKLG_MSR_IND  BKLG_MSR_IND, 

        a12.BUYER_ID  BUYER_NM,         

        a11.CNSMPN_FCLTY_CD  CNSMPN_FCLTY_CD, 

        CASE WHEN a11.SSC_CD ='BTS' AND a11.FGA_ID IS NOT NULL THEN 'FGA EMBEDDED'

             when a11.SSC_CD= '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 as 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)  PART_ITM_QTY 

        FROM ACTL_SALE_PART_SMRY_VW a11 

        JOIN  MFG_PART_ATTR  a12 

        ON (a11.CCN = a12.CCN ) 

        JOIN  FULFLT_RGN  a13 

        ON (a11.BUILD_FCLTY_CD = a13.FCLTY_CD AND 

        a11.INTER_CO_BU_ID = a13.INTER_CO_BU_ID) 

        JOIN CHNL_HIER    a14 

        ON (a11.INTER_CO_BU_ID = a14.BU_ID AND  

        a11.INTER_CO_LCL_CHNL_CD = a14.LCL_CHNL_CODE) 

        JOIN  PHYS_GEO_HIER    a15 

        ON (a14.RPTG_BU_ID = a15.BU_ID) 

        JOIN COMB_PROD_HIER    a16 

        ON (a11.BASE_SYS_CD = a16.COMB_HIER_CD) 

        JOIN  CORP_CLDR a17 

        ON (a11.PART_SLS_DT = a17.CLDR_DATE)          

        GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18) AS Source_tbl    

  ON (Target_tbl.SUB_RGN_DESC = Source_tbl.SUB_RGN_DESC

and Target_tbl.OPS_SALE_TYPE = Source_tbl.OPS_SALE_TYPE

and Target_tbl.ITM_DESC = Source_tbl.ITM_DESC

and Target_tbl.PART_ITM_NBR = Source_tbl.PART_ITM_NBR

and Target_tbl.FISC_MTH_VAL = Source_tbl.FISC_MTH_VAL)

  WHEN MATCHED THEN

     UPDATE SET PART_ITM_QTY = Source_tbl.PART_ITM_QTY

  WHEN NOT MATCHED THEN

     INSERT (Target_tbl.Pk_Key, Target_tbl.ANLST_ID,

Target_tbl.BKLG_MSR_IND,

Target_tbl.BUYER_NM,

Target_tbl.CNSMPN_FCLTY_CD,

Target_tbl.CustCol_3,

Target_tbl.EMBDD_FLG,

Target_tbl.SSC_CD,

Target_tbl.FULFLT_RGN_DESC,

Target_tbl.SUB_RGN_DESC,

Target_tbl.ISS_CD,

Target_tbl.OPS_SALE_TYPE,

Target_tbl.ITM_DESC,

Target_tbl.PART_ITM_NBR,

Target_tbl.FISC_MTH_VAL,

Target_tbl.FMLY_PARNT_PROD_TYPE_DESC,

Target_tbl.SYS_FLG,

Target_tbl.CCN,

Target_tbl.DERIVED_SLS_CHNL_VAL,

Target_tbl.PART_ITM_QTY

)

     VALUES ( NULL, Source_tbl.ANLST_ID,

Source_tbl.BKLG_MSR_IND,

Source_tbl.BUYER_NM,

Source_tbl.CNSMPN_FCLTY_CD,

Source_tbl.CustCol_3,

Source_tbl.EMBDD_FLG,

Source_tbl.SSC_CD,

Source_tbl.FULFLT_RGN_DESC,

Source_tbl.SUB_RGN_DESC,

Source_tbl.ISS_CD,

Source_tbl.OPS_SALE_TYPE,

Source_tbl.ITM_DESC,

Source_tbl.PART_ITM_NBR,

Source_tbl.FISC_MTH_VAL,

Source_tbl.FMLY_PARNT_PROD_TYPE_DESC,

Source_tbl.SYS_FLG,

Source_tbl.CCN,

Source_tbl.DERIVED_SLS_CHNL_VAL,

Source_tbl.PART_ITM_QTY);

Error message:-

Executed as Single statement.  Failed [5758 : HY000] The search condition must fully specify the Target table primary index and partition column(s) and expression must match INSERT specification primary index and partition column(s). 

Elapsed time = 00:00:00.096 

STATEMENT 1: MERGE  failed. 

Below columns form composite primary Key in target table;

Target_tbl.SUB_RGN_DESC

Target_tbl.OPS_SALE_TYPE

Target_tbl.ITM_DESC 

Target_tbl.PART_ITM_NBR

Target_tbl.FISC_MTH_VAL

Could you please help me understand the issue with above merge statement.

Thanks in advance

Rajesh Suyal


1 REPLY
Enthusiast

Re: Error in Merge Statement