MERGE statement problem with IDENTITY field on TARGET Table

Database

MERGE statement problem with IDENTITY field on TARGET Table

Hi all,

 I am facing some challenges with the MERGE statement.  I am trying to move data from a source table to a destintion table. 

 These are the DDLs of my two tables:

CREATE VOLATILE TABLE ZZ_SOURCE (
TEST_NUM VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
TEST_COMMENT_TXT VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
TEST_COMMENT_TYPE_CD CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL
) UNIQUE PRIMARY INDEX ("TEST_NUM", "TEST_COMMENT_TYPE_CD") ON COMMIT PRESERVE ROWS;

and

CREATE SET TABLE ODS_TABLES_TEST.TARGET, NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
TEST_COMMENT_ID INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY
(START WITH 1
INCREMENT BY 1
MINVALUE -2147483647
MAXVALUE 2147483647
NO CYCLE),
TEST_NUM VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
TEST_COMMENT_TYPE_CD CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
TEST_COMMENT_TXT VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
UPDATE_INTERFACE_NM VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
CREATE_DTTM TIMESTAMP(6) NOT NULL,
UPDATE_DTTM TIMESTAMP(6))
PRIMARY INDEX ( TEST_COMMENT_ID );

So my 1st MERGE statement looked like this:

MERGE INTO "ODS_TABLES_TEST"."TARGET" tt
USING
(
SELECT
"TEST_NUM"
,"TEST_COMMENT_TYPE_CD"
,"TEST_COMMENT_TXT"
,'1048 TEST COMMENT' AS "UPDATE_INTERFACE_NM"
,CURRENT_TIMESTAMP AS "CREATE_DTTM"
,CURRENT_TIMESTAMP AS "UPDATE_DTTM"
FROM
"ZZ_SOURCE"
) ss
ON (ss."TEST_NUM" = tt."TEST_NUM" AND ss."TEST_COMMENT_TYPE_CD" = tt."TEST_COMMENT_TYPE_CD")
WHEN MATCHED THEN UPDATE SET "TEST_COMMENT_TXT" = ss."TEST_COMMENT_TXT"
WHEN NOT MATCHED THEN INSERT ("TEST_NUM", "TEST_COMMENT_TYPE_CD", "TEST_COMMENT_TXT", "UPDATE_INTERFACE_NM", "CREATE_DTTM", "UPDATE_DTTM" )
values(ss."TEST_NUM", ss."TEST_COMMENT_TYPE_CD", ss."TEST_COMMENT_TXT", ss."UPDATE_INTERFACE_NM", ss."CREATE_DTTM", ss."UPDATE_DTTM");

and I got this error:

Failed [5758 : HY000] MyProcedure: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)

Because of this I modified my MERGE statement to look like this, to be able to use the TARGET primary index for the matching, but even this throws the same error

MERGE INTO "ODS_TABLES_TEST"."TARGET" tt
USING
(
SELECT
t."TEST_COMMENT_ID"
,zz."TEST_NUM"
,zz."TEST_COMMENT_TYPE_CD"
,zz."TEST_COMMENT_TXT"
,'xxxxxxx xxxxx' AS "UPDATE_INTERFACE_NM"
,CURRENT_TIMESTAMP AS "CREATE_DTTM"
,CURRENT_TIMESTAMP AS "UPDATE_DTTM"
FROM
"ZZ_SOURCE" zz
LEFT JOIN "ODS_TABLES_TEST"."TARGET" t ON
zz."TEST_NUM" = t."TEST_NUM"
AND zz."TEST_COMMENT_TYPE_CD" = t."TEST_COMMENT_TYPE_CD"
) ss
ON (ss."TEST_COMMENT_ID" = tt."TEST_COMMENT_ID")
WHEN MATCHED THEN UPDATE SET "TEST_COMMENT_TXT" = ss."TEST_COMMENT_TXT"
WHEN NOT MATCHED THEN INSERT ( "TEST_NUM", "TEST_COMMENT_TYPE_CD", "TEST_COMMENT_TXT", "UPDATE_INTERFACE_NM", "CREATE_DTTM", "UPDATE_DTTM" )
values(ss."TEST_NUM", ss."TEST_COMMENT_TYPE_CD", ss."TEST_COMMENT_TXT", ss."UPDATE_INTERFACE_NM", ss."CREATE_DTTM", ss."UPDATE_DTTM");

Now if I change my target table DDL to be something like this: (Remove the identiy field and change the primary index)

CREATE SET TABLE ODS_TABLES_TEST.TARGET, NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
TEST_NUM VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
TEST_COMMENT_TYPE_CD CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
TEST_COMMENT_TXT VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
UPDATE_INTERFACE_NM VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
CREATE_DTTM TIMESTAMP(6) NOT NULL,
UPDATE_DTTM TIMESTAMP(6))
PRIMARY INDEX ( TEST_NUM , TEST_COMMENT_TYPE_CD );

And execute my first merge statement it will work fine. 

Now I am not suppose to modify the TARGET DDL just because of this tiny issue :P 

How can I make the MERGE statement to work when having an identity column?

1 REPLY
Teradata Employee

Re: MERGE statement problem with IDENTITY field on TARGET Table