Non-updatable view with LEAD...

From: dean <deanbrown3d_at_yahoo.com>
Date: 3 Jun 2006 16:18:32 -0700
Message-ID: <1149376712.001361.168670@y43g2000cwc.googlegroups.com>

10g, Windows:

create table T (A number not null, B number not null, X number not null);

insert into T values (1,1,123);
insert into T values (2,2,123);
insert into T values (3,3,123);
insert into T values (4,4,123);

commit;

create or replace view V as
(
  select T.*, lead(B) OVER (ORDER BY B) as NEXT_B   from T
);

select * from V;

        A B X NEXT_B ---------- ---------- ---------- ----------

         1          1        123          2
         2          2        123          3
         3          3        123          4
         4          4        123



update V set X=567 where B=2
       *

ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view

I was hoping to use this kind of query in an app so that I can examine consecutive pairs of records (its a train's route schedule kind of thing). However, the view is not updatable. I can't see why the above view is not updatable, however, and this is a simplified version of the real view.

Any hints welcomed. I know how to do the self join, but using LEAD is faster and I was hoping to keep it.

Thanks for any tips,

Dean Received on Sat Jun 03 2006 - 18:18:32 CDT