Re: Any way to do this in straight SQL?

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.

--
http://www.freelists.org/webpage/oracle-l

Other related posts: