Problems with MERGE Statement

From: rishi <rrkapoor_at_hotmail.com>
Date: 18 May 2004 09:26:36 -0700
Message-ID: <c43d208f.0405180826.3db3c186@posting.google.com>

Has anyone had any problems with the MERGE statement inserting incorrect values. Below is a piece of my stored procedure

DBMS_OUTPUT.PUT_LINE('1 CustomerID '||cust_id||' Atrribute_id
'||attrib_id||' Value '||UTCDate);

MERGE INTO cust_attributes_old ca

        USING (SELECT cust_id customer_id, attrib_id attribute_id FROM DUAL) ca1

        ON (ca.customer_id = ca1.customer_id and ca.attribute_id = ca1.attribute_id)

	WHEN MATCHED THEN
		UPDATE SET ATTRIBUTE_VALUE=UTCDate, INSERT_DATETIME=SYSDATE,
INSERT_PROCESS='ETL', UPDATE_DATETIME=SYSDATE, UPDATE_PROCESS='ETL'
	WHEN NOT MATCHED THEN
		INSERT (CUSTOMER_ID,ATTRIBUTE_ID,ATTRIBUTE_VALUE,INSERT_DATETIME,INSERT_PROCESS,UPDATE_DATETIME,UPDATE_PROCESS)
			VALUES (cust_id,attrib_id,UTCDate,SYSDATE,'ETL',SYSDATE,'ETL');

insert into temp_hold values (cust_id,attrib_id,UTCDate); DBMS_OUTPUT.PUT_LINE('2 CustomerID '||cust_id||' Atrribute_id
'||attrib_id||' Value '||UTCDate);

In this case cust_id = 16721069, attrib_id = 315 and attribute_value = 0434. After running this script the values that get populated into cust_Attributes_old is cust_id = 434, attrib_id = 315 and attribute_value = 315. However the insert into the temp_hold table (for debugging) is what I would expect...below are the results.

exec ETL_CUSTATTRIB_STGTOTRG_ALT2v2
1 CustomerID 16721069 Atrribute_id 315 Value 0434 2 CustomerID 16721069 Atrribute_id 315 Value 0434

SQL> select * from temp_hold;

CUSTOMER_ID ATTRIBUTE_ID ATTRIBUTE_VALUE

----------- ------------ ------------------
   16721069          315 0434

SQL> select * from cust_attributes_old;

CUSTOMER_ID ATTRIBUTE_ID ATTRIBUTE_VALUE

----------- ------------ ----------------
        434          315 315

Received on Tue May 18 2004 - 11:26:36 CDT