Error Running Merge Statement

Database
tsk
Enthusiast

Error Running Merge Statement

Hi All,

I am trying to run a MERGE statement (to perform UPSERT) where one of the column in Primary Index is nullable. I am encountering the following error when I run the same:

"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)."

Please let me know what could be the reason for the same . Both my source and target tables does not have duplicates on the Primary Index so wasn't sure why I got this error msg. Also having a column as NULL in a composite primary index is the reason for this error or is there something else that could cause it.

Please provide your thoughts and early help would be much appreicated.

Thanks,

Kaushik

7 REPLIES
Highlighted
Senior Supporter

Re: Error Running Merge Statement

it is much easier to comment on these questions of you share the code and ddls - change names if you need.

Otherwise it is just a lot of guessing...

tsk
Enthusiast

Re: Error Running Merge Statement

Hi ulrich, The below is the SQL I am trying to run . I tried the following options to debug:

1)Changed NULL to Blank value and tried to run the SQL. Even then I got the same error.

2) Removed coalsece and it resulted in incorrect results where the record got inserted instead of updating exisiting record.

Please provide your suggestions on how to overcome this.

MERGE INTO SASI_DM_D1.CIM_NONOPT_ACTIVE AS A

USING SASI_STG_D1.CIM_NONOPT_TEMP AS  T

ON  (A.PTY_ID = T.PTY_ID

AND coalesce(a.gu_id,'x')=coalesce(t.gu_id,'x')

AND A.OFR_CD = T.OFR_CD

AND A.STRT_DT=T.STRT_DT

AND A.STAT_IN=T.STAT_IN)

WHEN MATCHED THEN

 UPDATE SET

  BTCH_ID = T.BTCH_ID

  ,ASSO_ACNT1 = T.ASSO_ACNT1

  ,ASSO_ACNT2 = T.ASSO_ACNT2

  ,ASSO_ACNT3 = T.ASSO_ACNT3

  ,ASSO_ACNT4 = T.ASSO_ACNT4

  ,ASSO_ACNT5 = T.ASSO_ACNT5

  ,ASSO_ACNT6 = T.ASSO_ACNT6

  ,ASSO_ACNT7 = T.ASSO_ACNT7

  ,ASSO_ACNT8 = T.ASSO_ACNT8

  ,ASSO_ACNT9 = T.ASSO_ACNT9

  ,ASSO_ACNT10 = T.ASSO_ACNT10

  ,ASSO_PROD1 = T.ASSO_PROD1

  ,ASSO_PROD2 = T.ASSO_PROD2

  ,ASSO_PROD3 = T.ASSO_PROD3

  ,ASSO_PROD4 = T.ASSO_PROD4

  ,ASSO_PROD5 = T.ASSO_PROD5

  ,ASSO_PROD6 = T.ASSO_PROD6

  ,ASSO_PROD7 = T.ASSO_PROD7

  ,ASSO_PROD8 = T.ASSO_PROD8

  ,ASSO_PROD9 = T.ASSO_PROD9

  ,ASSO_PROD10 = T.ASSO_PROD10

  ,END_DT = T.END_DT

  ,MDFD_DT = CAST(current_timestamp AS VARCHAR(20)) (TIMESTAMP(0))

  ,NO_OF_DAYS = T.NO_OF_DAYS

  ,PRIORITY_ID = T.PRIORITY_ID

  ,REC_TYP = T.REC_TYP

  ,TEMP_VALUE1 = T.TEMP_VALUE1

  ,TEMP_VALUE2 = T.TEMP_VALUE2

  ,TEMP_VALUE3 = T.TEMP_VALUE3

  ,TEMP_VALUE4 = T.TEMP_VALUE4

  ,TEMP_VALUE5 = T.TEMP_VALUE5

  ,TEMP_VALUE6 = T.TEMP_VALUE6

  ,TEMP_VALUE7 = T.TEMP_VALUE7

  ,TEMP_VALUE8 = T.TEMP_VALUE8

  ,TEMP_VALUE9 = T.TEMP_VALUE9

  ,TEMP_VALUE10 = T.TEMP_VALUE10

  ,TEMPLATE_ID = T.TEMPLATE_ID

  ,TEMPLATE_NO = T.TEMPLATE_NO

WHEN NOT MATCHED THEN

 INSERT

  (PTY_ID

  ,GU_ID

  ,OFR_CD

  ,STRT_DT

  ,BTCH_ID

  ,ASSO_ACNT1

  ,ASSO_ACNT2

  ,ASSO_ACNT3

  ,ASSO_ACNT4

  ,ASSO_ACNT5

  ,ASSO_ACNT6

  ,ASSO_ACNT7

  ,ASSO_ACNT8

  ,ASSO_ACNT9

  ,ASSO_ACNT10

  ,ASSO_PROD1

  ,ASSO_PROD2

  ,ASSO_PROD3

  ,ASSO_PROD4

  ,ASSO_PROD5

  ,ASSO_PROD6

  ,ASSO_PROD7

  ,ASSO_PROD8

  ,ASSO_PROD9

  ,ASSO_PROD10

  ,CRTD_DT

  ,END_DT

  ,MDFD_DT

  ,NO_OF_DAYS

  ,PRIORITY_ID

  ,REC_TYP

  ,STAT_IN

  ,TEMP_VALUE1

  ,TEMP_VALUE2

  ,TEMP_VALUE3

  ,TEMP_VALUE4

  ,TEMP_VALUE5

  ,TEMP_VALUE6

  ,TEMP_VALUE7

  ,TEMP_VALUE8

  ,TEMP_VALUE9

  ,TEMP_VALUE10

  ,TEMPLATE_ID

  ,TEMPLATE_NO)

 VALUES

  (T.PTY_ID

  ,T.GU_ID

  ,T.OFR_CD

  ,T.STRT_DT

  ,T.BTCH_ID

  ,T.ASSO_ACNT1

  ,T.ASSO_ACNT2

  ,T.ASSO_ACNT3

  ,T.ASSO_ACNT4

  ,T.ASSO_ACNT5

  ,T.ASSO_ACNT6

  ,T.ASSO_ACNT7

  ,T.ASSO_ACNT8

  ,T.ASSO_ACNT9

  ,T.ASSO_ACNT10

  ,T.ASSO_PROD1

  ,T.ASSO_PROD2

  ,T.ASSO_PROD3

  ,T.ASSO_PROD4

  ,T.ASSO_PROD5

  ,T.ASSO_PROD6

  ,T.ASSO_PROD7

  ,T.ASSO_PROD8

  ,T.ASSO_PROD9

  ,T.ASSO_PROD10

  ,T.CRTD_DT

  ,T.END_DT

  ,T.MDFD_DT

  ,T.NO_OF_DAYS

  ,T.PRIORITY_ID

  ,T.REC_TYP

  ,'A'

  ,T.TEMP_VALUE1

  ,T.TEMP_VALUE2

  ,T.TEMP_VALUE3

  ,T.TEMP_VALUE4

  ,T.TEMP_VALUE5

  ,T.TEMP_VALUE6

  ,T.TEMP_VALUE7

  ,T.TEMP_VALUE8

  ,T.TEMP_VALUE9

  ,T.TEMP_VALUE10

  ,T.TEMPLATE_ID

  ,T.TEMPLATE_NO);

tsk
Enthusiast

Re: Error Running Merge Statement

Here is the DDL

CREATE MULTISET TABLE sasi_dm_d1.cim_nonopt_active ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      PTY_ID VARCHAR(11) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      GU_ID VARCHAR(36) CHARACTER SET LATIN NOT CASESPECIFIC,

      OFR_CD VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      STRT_DT TIMESTAMP(0) FORMAT 'YYYY-MM-DDHH:Mi:SS' NOT NULL,

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

       BTCH_ID INTEGER NOT NULL,

      ASSO_ACNT1 CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '    ',

      ASSO_ACNT2 CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '    ',

      ASSO_ACNT3 CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '    ',

      ASSO_ACNT4 CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '    ',

      ASSO_ACNT5 CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '    ',

      ASSO_ACNT6 CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '    ',

      ASSO_ACNT7 CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '    ',

      ASSO_ACNT8 CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '    ',

      ASSO_ACNT9 CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '    ',

      ASSO_ACNT10 CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '    ',

      ASSO_PROD1 CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '   ',

      ASSO_PROD2 CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '   ',

      ASSO_PROD3 CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '   ',

      ASSO_PROD4 CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '   ',

      ASSO_PROD5 CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '   ',

      ASSO_PROD6 CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '   ',

      ASSO_PROD7 CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '   ',

      ASSO_PROD8 CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '   ',

      ASSO_PROD9 CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '   ',

      ASSO_PROD10 CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '   ',

      CRTD_DT TIMESTAMP(0) FORMAT 'YYYY-MM-DDHH:Mi:SS',

      END_DT TIMESTAMP(0) FORMAT 'YYYY-MM-DDHH:Mi:SS',

      MDFD_DT TIMESTAMP(0) FORMAT 'YYYY-MM-DDHH:Mi:SS',

      NO_OF_DAYS SMALLINT COMPRESS 0 ,

      PRIORITY_ID VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      REC_TYP CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS ('EX','FD','IN','RX'),

      TEMP_VALUE1 VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      TEMP_VALUE2 VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      TEMP_VALUE3 VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      TEMP_VALUE4 VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      TEMP_VALUE5 VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      TEMP_VALUE6 VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      TEMP_VALUE7 VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      TEMP_VALUE8 VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      TEMP_VALUE9 VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      TEMP_VALUE10 VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      TEMPLATE_ID CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '   ',

      TEMPLATE_NO SMALLINT COMPRESS 0 )

PRIMARY INDEX ( PTY_ID ,GU_ID ,OFR_CD ,STRT_DT,STAT_IN);

CREATE MULTISET TABLE SASI_STG_D1.CIM_NONOPT_TEMP ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      PTY_ID VARCHAR(11) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      GU_ID VARCHAR(36) CHARACTER SET LATIN NOT CASESPECIFIC,

      OFR_CD VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      STRT_DT TIMESTAMP(0) FORMAT 'YYYY-MM-DDHH:Mi:SS' NOT NULL,

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

       BTCH_ID INTEGER NOT NULL,

      ACTION_ID CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,

      ASSO_ACNT1 CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '    ',

      ASSO_ACNT2 CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '    ',

      ASSO_ACNT3 CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '    ',

      ASSO_ACNT4 CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '    ',

      ASSO_ACNT5 CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '    ',

      ASSO_ACNT6 CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '    ',

      ASSO_ACNT7 CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '    ',

      ASSO_ACNT8 CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '    ',

      ASSO_ACNT9 CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '    ',

      ASSO_ACNT10 CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '    ',

      ASSO_PROD1 CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '   ',

      ASSO_PROD2 CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '   ',

      ASSO_PROD3 CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '   ',

      ASSO_PROD4 CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '   ',

      ASSO_PROD5 CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '   ',

      ASSO_PROD6 CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '   ',

      ASSO_PROD7 CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '   ',

      ASSO_PROD8 CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '   ',

      ASSO_PROD9 CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '   ',

      ASSO_PROD10 CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '   ',

      CRTD_DT TIMESTAMP(0) FORMAT 'YYYY-MM-DDHH:Mi:SS',

      END_DT TIMESTAMP(0) FORMAT 'YYYY-MM-DDHH:Mi:SS',

      MDFD_DT TIMESTAMP(0) FORMAT 'YYYY-MM-DDHH:Mi:SS',

      NO_OF_DAYS SMALLINT COMPRESS 0 ,

      PRIORITY_ID VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      REC_TYP CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS ('EX','FD','IN','RX'),

      TEMP_VALUE1 VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      TEMP_VALUE2 VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      TEMP_VALUE3 VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      TEMP_VALUE4 VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      TEMP_VALUE5 VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      TEMP_VALUE6 VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      TEMP_VALUE7 VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      TEMP_VALUE8 VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      TEMP_VALUE9 VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      TEMP_VALUE10 VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      TEMPLATE_ID CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '   ',

      TEMPLATE_NO SMALLINT COMPRESS 0      )

PRIMARY INDEX ( PTY_ID ,GU_ID ,OFR_CD ,STRT_DT ,STAT_IN);

Senior Supporter

Re: Error Running Merge Statement

I am not 100 sure as I can't check it right know but I guess the problem is that you want to change the value of one field in the PK.

You set  STAT_IN = 'A'

in the Insert clause but I guess this is not allowed as this would change the PI in comparision to the PI which was used for the join.

Maybe worth the check by set dat_in to t.stat_in...

tsk
Enthusiast

Re: Error Running Merge Statement

Just to clarify, do you suggest in that in the insert clause I should have T.STAT_IN instead of hardcoding to 'A'.

Also I removed the STAT_IN from Update statement cloumns because it is a part of PI so by your suggestion PI should not be updated...but still no luck....

If this is what your suggestion is I did try it now but seems to getting the same error.

Thanks,

Kaushik

Senior Supporter

Re: Error Running Merge Statement

Hi, yes, this was what I meant.

I think you need also remove the coalesce(a.gu_id,'x')=coalesce(t.gu_id,'x')

and use a.gu_id = t.gu_id as the coalesce might cause a problem.

In short - you need to specify the whole PI in the on clause and you are not allowed to change the PI....

Just a style issue

 ,MDFD_DT = CAST(current_timestamp AS VARCHAR(20)) (TIMESTAMP(0))

should be

 ,MDFD_DT = current_timestamp(0)

Re: Error Running Merge Statement

So this is a limit?

You can't MERGE a table with one nullable column in the Primary Index?

Thanks and regards,

Matteo