HI Experts,
I have created a proc where it has only merge statement which updates/inserts data from source table(inc_store_traffic), when i ran the proc first time it has executed successfully and all the rows from source have been moved to newly created table(mstr_store_traffic_tb), when i ran the proc second time it has thrown the below error...Can you help me where exactly it is going wrong
code :
MERGE INTO mstr_store_traffic_tb m
USING (SELECT CASE
WHEN bu_code = 'VH' THEN 5
WHEN bu_code = 'CK' THEN 2
END
business_unit,
chain_id,
division || SUBSTR(store_number, -3) storecode,
processed_date,
hour,
traffic_count,
trans_to_date
FROM inc_store_traffic) c
ON (c.storecode = m.storecode)
WHEN MATCHED
THEN
UPDATE SET
m.chain_id = c.chain_id,
m.processed_date = c.processed_date,
m.hour = c.hour,
m.traffic_count = c.traffic_count,
m.trans_to_date = c.trans_to_date
WHEN NOT MATCHED
THEN
INSERT (business_unit,
chain_id,
storecode,
processed_date,
hour,
traffic_count,
trans_to_date)
VALUES (c.business_unit,
c.chain_id,
c.storecode,
c.processed_date,
c.hour,
c.traffic_count,
c.trans_to_date);
Connecting to the database PHMD.
ORA-30926: unable to get a stable set of rows in the source tables
ORA-06512: at "PHMD.CR_STORE", line 18
ORA-06512: at line 2
Process exited.