Re: row number with in cursor

Kevin Duffy schrieb am 07.10.2016 um 03:01:
> The cursor is defined as follows:
> 
>   currDR   CURSOR  (r  character(15),  sD date )  IS
>        select  rate_key, rate_date,  rate_value,
>            LAG(rate_date, 1 , null)   OVER w  as lag_r_date  ,
>            LAG(rate_value, 1, null)   Over w  as lag_r_value ,
>              ( LAG(rate_value, 1, null)   Over w /100  * ( rate_date -  LAG(rate_date, 1 , null)   OVER w )  )  / 360
as overnight_rate_return
 
>        from rate_quote
>        where rate_key  = r  and rate_date  >= sD
>        WINDOW w as (PARTITION BY  rate_key  order by rate_date  ROWS 1 PRECEDING )
>        order by 2   ;
> 
> Cursor works fine
> Could I add a row number to the above?


Sure, just add:
   ROW_NUMBER() OVER W AS RN 

As the overall result is ordered by the same expression as the window w, this should work without much overhead.

Thomas