All Forums Database
Inang 5 posts Joined 08/12
08 Aug 2012
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

 

 

 

 

ulrich 816 posts Joined 09/09
09 Aug 2012

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;

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Inang 5 posts Joined 08/12
09 Aug 2012

Thanks Ulrich..It wrkd

- GK

You must sign in to leave a comment.