Re: Any way to do this in straight SQL?

Hi,

I think someone will find a way to solve this using analytics, but if you're using 10g, this problem would be solved more tidily using the new MODEL clause. Using MODEL you could conceptually make a sort of recursive lag, where your column would be history_balance if status != 'PROCESSED', or the value of itself in the previous row if status = 'PROCESSED'. Sorry about the lack of detail - I'm a long way from an Oracle database at the moment.

Malcolm.

On 07/12/05, ryan_gaffuri_at_comcast.net <ryan_gaffuri_at_comcast.net> wrote:
>
> 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