Issue regarding Ordered Analytical Functions

Database
Enthusiast

Issue regarding Ordered Analytical Functions

Hi there, 

To explain you guys, i have created the below volatile table.

CREATE VOLATILE TABLE  TEMP1 

(c   integer,

dt1 date,

cnt integer

)on commit preserve rows;

insert into temp1(1, current_date-30,1);

insert into temp1(1,current_date-20,null);

insert into temp1(1,current_date-10,null);

insert into temp1(1,current_date-5,3);

insert into temp1(1,current_date-3,null);

insert into temp1(1,current_date-2,null);

sel * from temp1;

The output for this is

1 7/9/2012 1

1 7/19/2012 ?

1 7/29/2012 ?

1 8/3/2012 3

1 8/5/2012 ?

1 8/6/2012 ?

Now, here i want the null the to be replaced with the latest not null value.

I tried using 

  sel c,dt1, 

     case when cnt is null then min(cnt) over (partition by c order by dt1 rows between 1 preceding and 1 preceding)

       else cnt end

  from temp1

But for this,

the output is

1 7/9/2012 1

1 7/19/2012 1

1 7/29/2012 ?

1 8/3/2012 3

1 8/5/2012 3

1 8/6/2012 ?

Since I have given 1 preceding and 1 preceding, it is not replacing for all null values.But if i give 'rows between unbounded preceding and 1 preceding', then it will pick min value and replaces with it.But instead of that i wnat it to be replaced with the latest value..not the min value.

Please help....

-GK




2 REPLIES
Senior Supporter

Re: Issue regarding Ordered Analytical Functions

Try

select c,
dt1,
cast(
substr(
max(dt1!!cnt) over (partition by c order by dt1 rows between unbounded preceding and current row)
,11)
as integer)
as cnt
from temp1;

Enthusiast

Re: Issue regarding Ordered Analytical Functions

Thanks Ulrich..It wrkd

- GK