Teradata Dbase Error:5758

Database
Enthusiast

Teradata Dbase Error:5758

Hi All,

Mery X'mas and Wish you a very Advance Happy New Year

My Question :-

While Running MERGE INSERT INTO /UPDATE Statement , followinf error have appeared

Failed. 5758:  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)

However there are some thoughts already given to this , but have not solved my issue.

Here is the SQL Statement I am trying to Run

==================================

MERGE INTO REF_ESR_AGGRBALTHRSHLD AGGRBAL

USING

(

SELECT

DISTINCT

RP.ESR_SITE_COD,

RP.ESR_CAT_CDE,

RP.ESR_CAT_KEY,

CASE WHEN TRIM(A.ESR_ATRIB_CDE) = 'HSBC-GLOBAL-BUS-CDE' AND TRIM(A.ESR_ENT_CDE) = 'AGGRBALTHRSHLD' THEN SUBSTR(RP.ESR_CAT_ATRIB_RPTY,TRIM(A.ESR_ATRIB_STRT_POS),TRIM(A.ESR_ATRIB_LGTH)) ELSE '' END AS GLOBL_BUS_CDE,

CASE WHEN TRIM(B.ESR_ATRIB_CDE) = 'PROD-TYPE-CDE' AND TRIM(B.ESR_ENT_CDE) = 'AGGRBALTHRSHLD' THEN SUBSTR(RP.ESR_CAT_ATRIB_RPTY,TRIM(A.ESR_ATRIB_STRT_POS),TRIM(A.ESR_ATRIB_LGTH)) ELSE '' END AS PROD_TYP,

CASE WHEN TRIM(C.ESR_ATRIB_CDE) = 'PRV-SEG-IND-START' AND TRIM(C.ESR_ENT_CDE) = 'AGGRBALTHRSHLD' THEN SUBSTR(RP.ESR_CAT_ATRIB_RPTY,TRIM(A.ESR_ATRIB_STRT_POS),TRIM(A.ESR_ATRIB_LGTH)) ELSE '' END AS SEG_PREV_IND,

CASE WHEN TRIM(D.ESR_ATRIB_CDE) = 'PRD-OPN-DATE-IND' AND TRIM(D.ESR_ENT_CDE) = 'AGGRBALTHRSHLD' THEN SUBSTR(RP.ESR_CAT_ATRIB_RPTY,TRIM(A.ESR_ATRIB_STRT_POS),TRIM(A.ESR_ATRIB_LGTH)) ELSE '' END AS KEY_IND,

CASE WHEN TRIM(E.ESR_ATRIB_CDE) = 'PROC-NAME' AND TRIM(E.ESR_ENT_CDE) = 'AGGRBALTHRSHLD' THEN SUBSTR(RP.ESR_CAT_ATRIB_RPTY,TRIM(A.ESR_ATRIB_STRT_POS),TRIM(A.ESR_ATRIB_LGTH)) ELSE '' END AS PROC_NAME,

RP.START_DT AS REC_ACDT,

RP.END_DT AS REC_EDT,

RP.START_DT,

RP.END_DT,

RP.ESR_CAT_KEY_DESC AS AGGR_BAL_THLD_DESC,

CASE WHEN TRIM(F.ESR_ATRIB_CDE) = 'MIN-THRSHLD-MAX-VAL' AND F.ESR_ENT_CDE = 'AGGRBALTHRSHLD' THEN SUBSTR(RP.ESR_CAT_ATRIB_RPTY,TRIM(A.ESR_ATRIB_STRT_POS),TRIM(A.ESR_ATRIB_LGTH)) ELSE '' END AS MIN_THLD_MX_VAL_AMT,

CASE WHEN TRIM(G.ESR_ATRIB_CDE) = 'MIN-THRSHLD-SEG-IND' AND G.ESR_ENT_CDE = 'AGGRBALTHRSHLD' THEN SUBSTR(RP.ESR_CAT_ATRIB_RPTY,TRIM(A.ESR_ATRIB_STRT_POS),TRIM(A.ESR_ATRIB_LGTH)) ELSE '' END AS MIN_TSHLD_SEG_IND,

CASE WHEN TRIM(H.ESR_ATRIB_CDE) = 'HIGH-THRSHLD-MAX-VAL' AND H.ESR_ENT_CDE = 'AGGRBALTHRSHLD' THEN SUBSTR(RP.ESR_CAT_ATRIB_RPTY,TRIM(A.ESR_ATRIB_STRT_POS),TRIM(A.ESR_ATRIB_LGTH)) ELSE '' END AS HIGH_THLD_MAX_VAL_AMT,

CASE WHEN TRIM(I.ESR_ATRIB_CDE) = 'HIGH-THRSHLD-SEG-IND' AND I.ESR_ENT_CDE = 'AGGRBALTHRSHLD' THEN SUBSTR(RP.ESR_CAT_ATRIB_RPTY,TRIM(A.ESR_ATRIB_STRT_POS),TRIM(A.ESR_ATRIB_LGTH)) ELSE '' END AS HIGH_THLD_SEG_IND,

CASE WHEN TRIM(J.ESR_ATRIB_CDE) = 'MAX-THRSHLD-MAX-VAL' AND J.ESR_ENT_CDE = 'AGGRBALTHRSHLD' THEN SUBSTR(RP.ESR_CAT_ATRIB_RPTY,TRIM(A.ESR_ATRIB_STRT_POS),TRIM(A.ESR_ATRIB_LGTH)) ELSE '' END AS MAX_THLD_MX_VAL_AMT,

CASE WHEN TRIM(K.ESR_ATRIB_CDE) = 'MAX-THRSHLD-SEG-IND' AND K.ESR_ENT_CDE = 'AGGRBALTHRSHLD' THEN SUBSTR(RP.ESR_CAT_ATRIB_RPTY,TRIM(A.ESR_ATRIB_STRT_POS),TRIM(A.ESR_ATRIB_LGTH)) ELSE '' END AS MAX_THLD_SEG_IND

FROM

(

SELECT ESR_SITE_COD, ESR_CAT_CDE, ESR_CAT_KEY, START_DT,END_DT,ESR_CAT_ATRIB_RPTY,ESR_CAT_KEY_DESC FROM RAW_CDU_ESR_CAT_RPTY

WHERE TRIM(ESR_CAT_CDE)='AGGRBALTHRSHLD' AND TRIM(ESR_SITE_COD)='1'

GROUP BY ESR_SITE_COD, ESR_CAT_CDE, ESR_CAT_KEY, START_DT,END_DT,ESR_CAT_ATRIB_RPTY,ESR_CAT_KEY_DESC

) RP

INNER JOIN

(

SEL ESR_ENT_CDE,ESR_ATRIB_CDE,ESR_ATRIB_STRT_POS,ESR_ATRIB_LGTH

FROM RAW_CDU_ESR_ENT_ATRIB_MAP WHERE ESR_ENT_CDE='AGGRBALTHRSHLD' AND TRIM(ESR_ATRIB_CDE)='HSBC-GLOBAL-BUS-CDE'

AND TRIM(ESR_KEY_ATRIB_IND)='Y'

) A ON A.ESR_ENT_CDE=RP.ESR_CAT_CDE

INNER JOIN

(

SEL ESR_ENT_CDE,ESR_ATRIB_CDE,ESR_ATRIB_STRT_POS,ESR_ATRIB_LGTH

FROM RAW_CDU_ESR_ENT_ATRIB_MAP WHERE ESR_ENT_CDE='AGGRBALTHRSHLD' AND TRIM(ESR_ATRIB_CDE)='PROD-TYPE-CDE'

AND TRIM(ESR_KEY_ATRIB_IND)='Y'

) B ON B.ESR_ENT_CDE=RP.ESR_CAT_CDE

INNER JOIN

(

SEL ESR_ENT_CDE,ESR_ATRIB_CDE,ESR_ATRIB_STRT_POS,ESR_ATRIB_LGTH

FROM RAW_CDU_ESR_ENT_ATRIB_MAP WHERE ESR_ENT_CDE='AGGRBALTHRSHLD' AND TRIM(ESR_ATRIB_CDE)='PRV-SEG-IND-START'

AND TRIM(ESR_KEY_ATRIB_IND)='Y'

) C ON C.ESR_ENT_CDE=RP.ESR_CAT_CDE

INNER JOIN

(

SEL ESR_ENT_CDE,ESR_ATRIB_CDE,ESR_ATRIB_STRT_POS,ESR_ATRIB_LGTH

FROM RAW_CDU_ESR_ENT_ATRIB_MAP WHERE ESR_ENT_CDE='AGGRBALTHRSHLD' AND TRIM(ESR_ATRIB_CDE)='PRD-OPN-DATE-IND'

AND TRIM(ESR_KEY_ATRIB_IND)='Y'

) D ON D.ESR_ENT_CDE=RP.ESR_CAT_CDE

INNER JOIN

(

SEL ESR_ENT_CDE,ESR_ATRIB_CDE,ESR_ATRIB_STRT_POS,ESR_ATRIB_LGTH

FROM RAW_CDU_ESR_ENT_ATRIB_MAP WHERE ESR_ENT_CDE='AGGRBALTHRSHLD' AND TRIM(ESR_ATRIB_CDE)='PROC-NAME'

AND TRIM(ESR_KEY_ATRIB_IND)='Y'

) E ON E.ESR_ENT_CDE=RP.ESR_CAT_CDE

INNER JOIN

(

SEL ESR_ENT_CDE,ESR_ATRIB_CDE,ESR_ATRIB_STRT_POS,ESR_ATRIB_LGTH

FROM RAW_CDU_ESR_ENT_ATRIB_MAP WHERE ESR_ENT_CDE='AGGRBALTHRSHLD' AND TRIM(ESR_ATRIB_CDE)='MIN-THRSHLD-MAX-VAL'

AND TRIM(ESR_KEY_ATRIB_IND)='N'

) F ON F.ESR_ENT_CDE=RP.ESR_CAT_CDE

INNER JOIN

(

SEL ESR_ENT_CDE,ESR_ATRIB_CDE,ESR_ATRIB_STRT_POS,ESR_ATRIB_LGTH

FROM RAW_CDU_ESR_ENT_ATRIB_MAP WHERE ESR_ENT_CDE='AGGRBALTHRSHLD' AND TRIM(ESR_ATRIB_CDE)='MIN-THRSHLD-SEG-IND'

AND TRIM(ESR_KEY_ATRIB_IND)='N'

) G ON G.ESR_ENT_CDE=RP.ESR_CAT_CDE

INNER JOIN

(

SEL ESR_ENT_CDE,ESR_ATRIB_CDE,ESR_ATRIB_STRT_POS,ESR_ATRIB_LGTH

FROM RAW_CDU_ESR_ENT_ATRIB_MAP WHERE ESR_ENT_CDE='AGGRBALTHRSHLD' AND TRIM(ESR_ATRIB_CDE)='HIGH-THRSHLD-MAX-VAL'

AND TRIM(ESR_KEY_ATRIB_IND)='N'

) H ON H.ESR_ENT_CDE=RP.ESR_CAT_CDE

INNER JOIN

(

SEL ESR_ENT_CDE,ESR_ATRIB_CDE,ESR_ATRIB_STRT_POS,ESR_ATRIB_LGTH

FROM RAW_CDU_ESR_ENT_ATRIB_MAP WHERE ESR_ENT_CDE='AGGRBALTHRSHLD' AND TRIM(ESR_ATRIB_CDE)='HIGH-THRSHLD-SEG-IND'

AND TRIM(ESR_KEY_ATRIB_IND)='N'

) I ON I.ESR_ENT_CDE=RP.ESR_CAT_CDE

INNER JOIN

(

SEL ESR_ENT_CDE,ESR_ATRIB_CDE,ESR_ATRIB_STRT_POS,ESR_ATRIB_LGTH

FROM RAW_CDU_ESR_ENT_ATRIB_MAP WHERE ESR_ENT_CDE='AGGRBALTHRSHLD' AND TRIM(ESR_ATRIB_CDE)='MAX-THRSHLD-MAX-VAL'

AND TRIM(ESR_KEY_ATRIB_IND)='N'

) J ON J.ESR_ENT_CDE=RP.ESR_CAT_CDE

INNER JOIN

(

SEL ESR_ENT_CDE,ESR_ATRIB_CDE,ESR_ATRIB_STRT_POS,ESR_ATRIB_LGTH

FROM RAW_CDU_ESR_ENT_ATRIB_MAP WHERE ESR_ENT_CDE='AGGRBALTHRSHLD' AND TRIM(ESR_ATRIB_CDE)='MAX-THRSHLD-SEG-IND'

AND TRIM(ESR_KEY_ATRIB_IND)='N'

) K ON K.ESR_ENT_CDE=RP.ESR_CAT_CDE

LEFT OUTER JOIN

REF_ESR_AGGRBALTHRSHLD AGGRBAL

ON TRIM(RP.ESR_CAT_CDE) = TRIM(AGGRBAL.ESR_CAT_CDE)

) AS X

ON

TRIM(AGGRBAL.GLOBL_BUS_CDE)=TRIM(X.GLOBL_BUS_CDE)

AND

TRIM(AGGRBAL.PROD_TYP)=TRIM(X.PROD_TYP)

AND

TRIM(AGGRBAL.SEG_PREV_IND)=TRIM(X.SEG_PREV_IND)

AND

TRIM(AGGRBAL.KEY_IND)=TRIM(X.KEY_IND)

AND

TRIM(AGGRBAL.PROC_NAME)=TRIM(X.PROC_NAME)

AND

X.START_DT= AGGRBAL.START_DT

WHEN MATCHED THEN UPDATE

SET

END_DT = X.END_DT,

REC_EDT=X.END_DT

WHEN NOT MATCHED THEN

INSERT

(

ESR_SITE_COD,

ESR_CAT_CDE,

GLOBL_BUS_CDE,

PROD_TYP,

SEG_PREV_IND,

KEY_IND,

PROC_NAME,

REC_ACDT,

REC_EDT,

START_DT,

END_DT,

AGGR_BAL_THLD_DESC,

MIN_THLD_MX_VAL_AMT,

MIN_TSHLD_SEG_IND,

HIGH_THLD_MAX_VAL_AMT,

HIGH_THLD_SEG_IND,

MAX_THLD_MX_VAL_AMT,

MAX_THLD_SEG_IND

)

VALUES

(

X.ESR_SITE_COD,

X.ESR_CAT_CDE,

X.GLOBL_BUS_CDE,

X.PROD_TYP,

X.SEG_PREV_IND,

X.KEY_IND,

X.PROC_NAME,

X.REC_ACDT,

X.REC_EDT,

X.START_DT,

X.END_DT,

X.AGGR_BAL_THLD_DESC,

X.MIN_THLD_MX_VAL_AMT,

X.MIN_TSHLD_SEG_IND,

X.HIGH_THLD_MAX_VAL_AMT,

X.HIGH_THLD_SEG_IND,

X.MAX_THLD_MX_VAL_AMT,

X.MAX_THLD_SEG_IND

);

Following is the DDL of the target table, where I am trying to insert/update/merge the data into

==================================================================

CREATE MULTISET TABLE GB_DWHS_SILO36_DB01.REF_ESR_AGGRBALTHRSHLD ,NO FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL,

CHECKSUM = DEFAULT,

DEFAULT MERGEBLOCKRATIO

(

ESR_SITE_COD SMALLINT NOT NULL,

ESR_CAT_CDE CHAR(20) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS 'AGGRBALTHRSHLD ',

GLOBL_BUS_CDE CHAR(5) CHARACTER SET LATIN CASESPECIFIC NOT NULL,

PROD_TYP CHAR(5) CHARACTER SET LATIN CASESPECIFIC NOT NULL,

SEG_PREV_IND CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL,

KEY_IND CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL,

PROC_NAME CHAR(5) CHARACTER SET LATIN CASESPECIFIC NOT NULL,

REC_ACDT DATE FORMAT 'YYYY-MM-DD' NOT NULL,

REC_EDT DATE FORMAT 'YYYY-MM-DD' NOT NULL DEFAULT DATE '9999-12-31' COMPRESS (DATE '9999-12-31'),

REC_TS TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),

START_DT DATE FORMAT 'YYYY-MM-DD' NOT NULL DEFAULT CURRENT_TIMESTAMP(6),

END_DT DATE FORMAT 'YYYY-MM-DD' NOT NULL DEFAULT CURRENT_TIMESTAMP(6),

AGGR_BAL_THLD_DESC VARCHAR(50) CHARACTER SET LATIN CASESPECIFIC NOT NULL,

MIN_THLD_MX_VAL_AMT CHAR(19) CHARACTER SET LATIN CASESPECIFIC COMPRESS ,

MIN_TSHLD_SEG_IND VARCHAR(20) CHARACTER SET LATIN CASESPECIFIC,

HIGH_THLD_MAX_VAL_AMT CHAR(19) CHARACTER SET LATIN CASESPECIFIC COMPRESS ,

HIGH_THLD_SEG_IND VARCHAR(20) CHARACTER SET LATIN CASESPECIFIC,

MAX_THLD_MX_VAL_AMT CHAR(19) CHARACTER SET LATIN CASESPECIFIC COMPRESS ,

MAX_THLD_SEG_IND VARCHAR(20) CHARACTER SET LATIN CASESPECIFIC)

PRIMARY INDEX ( GLOBL_BUS_CDE ,PROD_TYP ,SEG_PREV_IND ,KEY_IND ,

PROC_NAME )

UNIQUE INDEX PKCols ( ESR_SITE_COD ,ESR_CAT_CDE ,GLOBL_BUS_CDE ,

PROD_TYP ,SEG_PREV_IND ,KEY_IND ,PROC_NAME ,REC_ACDT );

Anticipating response

Many thanks

Regards

Swathi Reddy

5 REPLIES
Enthusiast

Re: Teradata Dbase Error:5758

Hi Swathi,

Your query is very long and I see a lot of trim :). Since you know the joining condtions and fields very well, an explanation is given in this link:

http://www.info.teradata.com/HTMLPubs/DB_TTU_14_00/index.html#page/General_Reference/B035_1096_111A/...

Explanation:

The search condition must fully specify an equality constraint on the primary index and partition column(s) between the source and target table. Other set of conditions must be ANDed with primary index and partition column conditions. If an expression/column is selected for the target primary index in the INSERT part of the MERGE statement, that expression must be matched with the target primary index on the ON clause. Expression should also not involve a non-deterministic UDF/UDM or RANDOM function

 

Cheers,

Raja
Enthusiast

Re: Teradata Dbase Error:5758

Hi Raja,

Basically I am making sure that if any change happens my End date will be altered and new record will be inserted

While Inserting  , I am making sure that ALL PI and KEY columns are covered and inserted if any Key value comes to form a new row in target table

Basically , the Target table REF_ESR_AGGRBALTHRSHLD is having PI on (GLOBL_BUS_CDE ,PROD_TYP ,SEG_PREV_IND ,KEY_IND ,

PROC_NAME)

While loading , I am following all checks for the above columns

============================================

ON

TRIM(AGGRBAL.GLOBL_BUS_CDE)=TRIM(X.GLOBL_BUS_CDE)

AND

TRIM(AGGRBAL.PROD_TYP)=TRIM(X.PROD_TYP)

AND

TRIM(AGGRBAL.SEG_PREV_IND)=TRIM(X.SEG_PREV_IND)

AND

TRIM(AGGRBAL.KEY_IND)=TRIM(X.KEY_IND)

AND

TRIM(AGGRBAL.PROC_NAME)=TRIM(X.PROC_NAME)

Can you please pin point the error?

Regards

Swathi

Enthusiast

Re: Teradata Dbase Error:5758

Hi Swathi,

You can try and run the query and verify with data , step by step and make the changes in INSERT and see. I guess the update is fine.

Dont run the entire stuff, just run the query and verify with data, marking that you have left outer join with parent table, select distinct and lot of substring. I am not sure of the perforamnce.

Cheers,

Raja

Junior Contributor

Re: Teradata Dbase Error:5758

Hi Swathi,

why do you need all those TRIMs? Do you have leading blanks in your data or non matching data types?

With TRIM you're simply not matching the PI of the target table.

Enthusiast

Re: Teradata Dbase Error:5758

Hello Dieter

Perfect !! Just removed TRIM keyword from ON statement and it worked !! I just thought to have a exact match , if there are any space.. say for example a field is char 30 and it has only 5 chars in it..something like that..

Many Many Thanks for commenting and taking your time on this

Many Thanks Raja as well for your time

I have just grown more confident on this website...

Regards

Swathi Reddy