Updateable Views

[genesys]

I've created a table with the following structure:

center_code VARCHAR2(5) NOT NULL, -- PK
account_code VARCHAR2(10) NOT NULL,-- PK
acquisition_code VARCHAR2(3) NOT NULL,-- PK
pickup_type_code VARCHAR2(2) NOT NULL,-- PK
service_group_code VARCHAR2(4) NOT NULL,-- PK
exterior_packing_code VARCHAR2(2) NOT NULL,-- PK
pricing_code VARCHAR2(2) NOT NULL,-- PK
comm_res_code VARCHAR2(2) NOT NULL,-- PK
ars_code VARCHAR2(2) NOT NULL,-- PK
cust_size_segment_code VARCHAR2(2) NOT NULL,-- PK
saturday_pickup_flag CHAR(1) NOT NULL,-- PK
cpu_flag NUMBER DEFAULT 0 NOT NULL,
region_code VARCHAR2(2),
district_code VARCHAR2(2),
pkg_nbr NUMBER,
zero_pickup_nbr NUMBER(12,4),
lc_pkg_stop_nbr NUMBER,
equiv_stop_nbr NUMBER(12,4),
data_set_id NUMBER NOT NULL

Using this table and a few others, I've created the following view:

CREATE OR REPLACE VIEW cam3.vw_pud_detail_summary (
center_code, account_code, acquisition_code, pickup_type_code,
service_group_code, exterior_packing_code, pricing_code, comm_res_code,
ars_code, cust_size_segment_code, saturday_pickup_flag, cpu_flag,
region_code, district_code, pkg_nbr, zero_pickup_nbr, lc_pkg_stop_nbr,
equiv_stop_nbr, data_set_id, total_pkg_nbr, total_stop_nbr, cubic_ft_nbr,
weekly_stop_nbr, freq_data_set_id, est_stop_nbr )
AS
select dtl.*, summ.total_pkg_nbr, summ.total_stop_nbr, summ.cubic_ft_nbr,
ptf.weekly_stop_nbr, ptf.data_set_id freq_data_set_id, zcr.est_stop_nbr
from mtc_pud_detail_int dtl, mtc_pud_summary_int summ, ie_pickup_type_frequency ptf,
ie_pud_zpu_conversion_rate zcr
where dtl.data_set_id = summ.data_set_id
AND dtl.center_code = summ.center_code
AND dtl.account_code = summ.account_code
AND dtl.acquisition_code = summ.acquisition_code
AND dtl.pickup_type_code = summ.pickup_type_code
AND dtl.cust_size_segment_code = summ.cust_size_segment_code
AND dtl.saturday_pickup_flag = summ.saturday_pickup_flag
and summ.total_pkg_nbr = zcr.pkg_nbr(+)
and dtl.acquisition_code = ptf.acquisition_code
AND dtl.pickup_type_code = ptf.pickup_type_code
AND dtl.saturday_pickup_flag = ptf.saturday_pickup_flag

Upon attempting to update the equiv_stop_nbr column in the view, I receive the error "ora-01779 cannot modify a column which is maps to a non-key preserved table".

How do I update this column in the view?

Thanks,
Sean

[sambavan]

Please refer: RULE FOR DML STATEMENT ON JOIN VIEWS

http://www.oradoc.com/ora816/appdev....g03sch.htm#978

Sam

[Raminder]

You can use Instead of triggers to update base tables from complex views.
I read a very interesting article on Updatable Views in Oracle Magazine (Mar/Apr 2001) issue if I remeber correctly.

Go to:

http://www.Oracle.com/oramag

[Raminder]

Creating Updatable Views

By Steve Bobrowski

http://oracle.com/oramag/oracle/01-m...ml?o21o8i.html


All the best!