Troubles with MERGE

Database

Troubles with MERGE

Folks

Having some difficulty with a MERGE operation.  We keep getting the following error, but cannot see why:

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

Target table in the merge is DW_PRGM_YR:

CREATE SET TABLE WHS.DW_PRGM_YR ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL,

CHECKSUM = DEFAULT, DEFAULT MERGEBLOCKRATIO

(

SUR_PRGM_YR_NBR INTEGER NOT NULL,

OCCN_CD CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,

OCCN_DESC VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

PRGM_YR INTEGER NOT NULL DEFAULT 9999 ,

START_DT DATE FORMAT 'YY/MM/DD',

END_DT DATE FORMAT 'YY/MM/DD',

RPT_START_DT DATE FORMAT 'YY/MM/DD',

RPT_END_DT DATE FORMAT 'YY/MM/DD',

CREAT_BY VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

CREAT_TS TIMESTAMP(6) NOT NULL,

UPDT_BY VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,

UPDT_TS TIMESTAMP(6),

OMS_LOAD_DT DATE FORMAT 'YY/MM/DD')

UNIQUE PRIMARY INDEX ( SUR_PRGM_YR_NBR );

We have a temp table that we'd like to merge DW_PRGM_YR.  We have the select providing us the old PI value for updates (SUR_PRGM_YR_NBR) as well as generating a new surrogate for INSERTs (see new_Sur_Prgm_Yr_Nbr).  Our merge_condition is on the sur_prgm_yr_nbr fields.  Our UPDATE is not referencing the SUR field; the INSERT of course inserts all fields.

MERGE INTO WHS.DW_PRGM_YR AS t USING (

              SELECT

                     ROW_NUMBER() OVER (order by Y.SUR_PRGM_YR_NBR) + (SELECT MAX(SUR_PRGM_YR_NBR) FROM           WHS.DW_PRGM_YR) AS new_Sur_Prgm_Yr_Nbr,

                  Y.SUR_PRGM_YR_NBR AS OLD_SUR_PRGM_YR_NBR,

                  X.Prgm_Yr AS NEW_PRGM_YR,

                  X.Occn_Cd AS NEW_OCCN_CD,

                  X.Occn_Desc AS NEW_OCCN_DESC,

                  X.Start_Dt AS NEW_START_DT,

                  X.End_Dt AS NEW_END_DT

              FROM SEVEN.TEST_TEMP_TABLE_2 AS X

              LEFT OUTER JOIN

                     WHS.DW_PRGM_YR AS Y

              ON (X.PRGM_YR = Y.Prgm_Yr AND X.OCCN_CD = Y.Occn_Cd)

       ) AS s

    ON (t.SUR_PRGM_YR_NBR = s.OLD_SUR_PRGM_YR_NBR )

    WHEN MATCHED THEN

        UPDATE SET  OCCN_DESC = s.NEW_OCCN_DESC,

                     START_DT = s.NEW_START_DT,

                     END_DT = s.NEW_END_DT,

                     UPDT_BY = 'BTEQ',

                     UPDT_TS = current_timestamp

       WHEN NOT MATCHED THEN

              INSERT (SUR_PRGM_YR_NBR,OCCN_CD,OCCN_DESC,PRGM_YR,START_DT,END_DT,RPT_START_DT,RPT_END_DT,CREAT_BY,CREAT_TS,UPDT_BY,UPDT_TS,OMS_LOAD_DT)

              VALUES (s.new_Sur_Prgm_Yr_Nbr,s.NEW_OCCN_CD, s.NEW_OCCN_DESC, s.NEW_PRGM_YR, s.NEW_START_DT, s.NEW_END_DT, null, null, 'BTEQ', current_timestamp, null, null ,null);

Stumped as to why this MERGE statement is not working.  Any thoughts or advice?  Just can't see what we are missing...

Thx!

Joe

Tags (2)