Re: Any way to do this in straight SQL?

Hi List,

> There is probably a more elegant way to do it, ...

I guess the IGNORE NULLS clause (10g) was introduced with the intention to avoid the MAX .. OVER() trick to eliminate NULLS.

I'd propose something like this ..

SQL> select history_id,
  2 history_sequence,
  3 history_status,
  4 case when history_status != 'PROCESSED' then   5 history_balance
  6 else -- use history_balance from last non processed status   7 last_value(case when history_status != 'PROCESSED' then history_balance end ignore nulls)
  8 over (partition by history_id order by history_sequence)   9 end as history_balance
 10 from history
 11 order by history_id, history_sequence;

HISTORY_ID HISTORY_SEQUENCE HISTORY_STATUS HISTORY_BALANCE

---------- ---------------- -------------------- ---------------
         1              123 HISTORY 1                         10
         1              128 PROCESSED                         10
         1              130 PROCESSED                         10
         1              131 HISTORY 8                         15
         1              145 PROCESSED                         15

Elapsed: 00:00:00.57

Regards,

Jaromir
----- Original Message -----
From: "Kristian Myllymäki" <kristian_at_kmja.com> To: <ryan_gaffuri_at_comcast.net>
Cc: <oracle-l_at_freelists.org>
Sent: Wednesday, December 07, 2005 11:56 PM Subject: Re: Any way to do this in straight SQL?

>

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