lag and update

hello group,

10g:

i've got this table "z" with following attributes:

id
dat ... date value, not necessarily consecutive k ... some numeric value
k_vt

the first 3 columns are filled, k_vt shall be filled with value of k of the previous day.

I can't do it like this because of ora-30483:

update t
set k_vt=lag(k,1,null) over (partition by id order by id,dat)

This also does not work, as the subselect understandably always returns NULL

update z z1

   set k_vt=
(
select lag(k,1,null) over (partition by id order by id,dat)   from z z2
 where z1.id=z2.id
   and z1.dat=z2.dat
)

I'm thinking about using some sort of staging table to solve this - or how could i do this within one update statement?

thanks,
stephan Received on Wed Jul 16 2008 - 10:30:53 CDT