Re: Non-updatable view with LEAD...

dean wrote:
> 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

Use an instead-of trigger.

Demo at www.psoug.org in Morgan's Library under INSTEAD-OF TRIGGER.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org