Re: Any way to do this in straight SQL?
- From: Kristian Myllymäki <kristian@xxxxxxxx>
- To: ryan_gaffuri@xxxxxxxxxxx
- Date: Wed, 7 Dec 2005 23:56:41 +0100 (CET)
There is probably a more elegant way to do it, but you could "group" your
data into sets using some analytic functions that apply your logic. Then
use this result in an update or merge statement.
select
history_id,
history_sequence,
history_status,
first_value(history_balance) over (partition by grp order by
history_sequence) balance
from (
select
history_id,
history_sequence,
history_status,
history_balance,
max(r) over (order by history_id, history_sequence) grp
from (
select
history_id,
history_sequence,
history_status,
history_balance,
case when history_status != 'PROCESSED' then rownum
else null end r
from history
order by history_id, history_sequence
)
)
/
/Kristian
> Is it possible to use lag, but you don't know how many rows you want to go
> back?
> create table history (
> history_id number,
> history_sequence number,
> history_status varchar2(20),
> history_balance number);
> insert into history(1,123,'HISTORY 1',10);
> insert into history(1,128,'PROCESSED',0);
> insert into history(1,130,'PROCESSED',0);
> insert into history(1,131,'HISTORY 8',15);
> insert into history(1,145,'PROCESSED',0);
> for each history_id ordered by history_sequence
> loop
> if status = 'PROCESSED' then
> history_balance = the history_balance of the last record where
> status != 'PROCESSED'
> end if;
> end loop;
> Typically with lag you have to state how many rows you are looking back,
> in this case my discriminator is based on the value in the status field?
> After this is run, I expect the values to be
> 1,123,'HISTORY 1',10
> 1,128,'PROCESSED',10
> 1,130,'PROCESSED',10
> 1,131,'HISTORY 8',15
> 1,145,'PROCESSED',15
> I can do this with pl/sql. I am trying to figure out how to do this with
> straight sql.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
