Re: Analytical Function: select last value that is not null

From: Karl & Betty Schendel <schendel_at_kbcomputer.com>
Date: Sat, 11 May 2002 02:39:48 GMT
Message-ID: <schendel-FEED5D.22395810052002@netnews.attbi.com>

In article <332bb004.0205100805.7eb9bc5a_at_posting.google.com>, marcel.kraupp_at_gmx.ch (Marcel Kraupp) wrote:

> I have this problem (on 8i).
> There is a table x having an attribute d (for date) and n (for number):
>
> create table x (d date, n number);

>[snip]

> What I want is to select d,n from x order by d so that it returns
> the last n that was not null.
>
>
> I figure, if this is possible, it must be achieved with analytical
> functions (thoug I might be wrong).

You don't need to get that fancy. Given an ifnull(x,y) that returns x unless it's null, in which case it returns y (I forget what Oracle calls this), you can come close with:

select t1.d, ifnull(t1.n,t2.n)
from x t1, x t2
where t2.d = (select max(t3.d) from x t3

where t3.d <= t1.d and t3.n is not null)

which gives you everything except the first couple rows before a row with the first non-null n. I'm sure that's repairable but I don't see it in 30 seconds or less.

Karl Received on Fri May 10 2002 - 21:39:48 CDT