dup_val_on_index
331074Sep 5 2006 — edited Sep 7 2006I have a history table that gets populated by 4 different systems through SQL/LDR.
It first uploads a intermediate table and then finally populates the history table.Initally the query was retrun to handle duplicate values just by setting it them to whatever the current is in the staging table.But later realized that in some situations its overwriting some columns to null because the data from four systems might have some columns that might be null.I want to change the query in a way that during dup_val_on_index if a column being updated has some value and the staging table has null leave it with the value rather than null.
Listed below is the sample code
exception
when dup_val_on_index then
update hist_winchester set salesordernumber=wc.salesordernumber,
pur_ord_num=wc.pur_ord_num,
dest_city=wc.dest_city
where hist_winchester.deliverynumber=wc.deliverynumber and
hist_winchester.material_num=wc.material_num;
end;
end loop;
commit;
exception
when others then
rollback;
end load_hist_winchester_tab;
Any help is appreciated
Ganesh