Re: Any way to do this in straight SQL?

From: Kristian Myllymäki <kristian_at_kmja.com>
Date: Wed, 7 Dec 2005 23:56:41 +0100 (CET)
Message-ID: <22874.85.224.137.70.1133996201.squirrel@glenlivet.oderland.com>

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

Received on Wed Dec 07 2005 - 16:59:53 CST