Tony wrote:
> Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1084942474.929841_at_yasure>...
>
>>rishi wrote:
>>
>>
>>>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
>>
>>Take a good look at your update statement:
>>
>>UPDATE SET ATTRIBUTE_VALUE=UTCDate, INSERT_DATETIME=SYSDATE,
>>INSERT_PROCESS='ETL', UPDATE_DATETIME=SYSDATE, UPDATE_PROCESS='ETL'
>>
>>Do you see a WHERE clause?
>
>
> No WHERE clause is required in the UPDATE part of a MERGE statement -
> the ON clause does that - so that isn't Rishi's problem. I don't know
> what is though.
Well you're correct. But I've neve seen a statement like this before:
SELECT cust_id customer_id, attrib_id attribute_id FROM DUAL
My experience is more like:
USING (
SELECT employee_id, salary
FROM employee
WHERE dept_no =20) E
ON (B.employee_id = E.employee_id)
Which is part of the Oracle demo.
--
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)
Received on Thu May 20 2004 - 00:11:28 CDT
