Re: lag and update

On 16 Jul., 22:28, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> steph schrieb:
>
>
>
> > 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
>
> Just nest your subquery one level more:
>
> update z z1
> set k_vt=(select k_vt
> from (select
> id,
> dat,
> k,
> lag(k,1,null) over(partition by id order by dat) k_vt
> from z)
> where z1.id=id and z1.dat=dat and z1.k=k
> )
>
> Btw, you don't need to order by id if you partition by id. If the
> combination of id,dat and k is not unique, then you may get more than
> one row in your subquery, it can be workarounded, if you update based on
> rowid.
>
> Best regards
>
> Maxim

Thanks a lot, this works perfect :-) Received on Thu Jul 17 2008 - 03:26:35 CDT