analytical functions

[seenumahesh22]

Hi,
I have a doubt regarding a calculation by using analytical functions.

((1+r1)+(1+r2)+ (1+r3)+....(1+r n) )*1000

where r1,r2,r3 are the values of a column in my table.
I need to get the values like this

first row (1+r1)*1000
secondrow ((1+r1)+(1+r2)) *1000
.
.
.
.
nth row ((1+r1)+(1+r2)+ (1+r3)+....(1+r n) )*1000

suppose you take the table as below

FUNDCODE FUNDRETURNS TIMEKEY

F1 10 200
F1 20 300
F1 40 400
F1 20 500
F1 3 600
F1 60 700
F1 70 800
F1 85 900
F1 90 1000
F1 23 1001


after applying the query
[code=oracle]

select a.*,sum((fundre turns+1)*1000) over (partition by fundcode order by timekey) runningtotal from fund_returns a

[/code]

i got the output for the above calculation as
FUNDCODE FUNDRETURNS TIMEKEY RUNNINGTOTAL
F1 10 200 11000
F1 20 300 32000
F1 40 400 73000
F1 20 500 94000
F1 3 600 98000
F1 60 700 159000
F1 70 800 230000
F1 85 900 316000
F1 90 1000 407000
F1 23 1001 431000

Now I need to do a similar type but the calculation(mul tiplication instead of summation) is
(1+r1)*(1+r2)*( 1+r3)*....(1+r n) *1000

Is it doable using analytical funtions ??? if so please throw some light on it.

Thanks in advance
Srinivasan M

[amitpatel66]

Hi Srinivasan,

Welcome to TSDN!!

Please make sure you follow POSTING GUIDELINES every time you post in this forum.

Thanks
MODERATOR

[amitpatel66]

I am not sure how you get the product using any analytical function but I have done using pipelined functions as shown below:

[code=oracle]

SQL> select * from xyz;

FC FR TK
-- --------- ---------
F1 10 100
F1 20 200
F1 30 300
F1 40 400

SQL> select text from user_source where name = 'RMUL';

TEXT
----------------------------------------------------------------------------------------------------
TYPE rmul IS TABLE OF INTEGER;

SQL> select text from user_source WHERE name = 'MULT';

TEXT
----------------------------------------------------------------------------------------------------
FUNCTION mult RETURN rmul PIPELINED IS
res NUMBER:= 1;
BEGIN
FOR I IN (SELECT fr+1 fn FROM xyz) LOOP
res:= res * I.fn * 1000;
PIPE ROW(res);
END LOOP;
return;
END;

9 rows selected.

SQL> select * FROM TABLE(mult);

COLUMN_VALUE
------------
11000
231000000
7.161E+12
2.936E+17

SQL>

[/code]

I hope this helps!!

[chritzuk]

Hi Srinivasn,

I think this will get you what you need (assumes fundreturns is always > 0):

SELECT   f.fundcode,
         f.fundreturns,
         f.timekey,
         EXP (SUM (LN (f.fundreturns + 1)) OVER (PARTITION BY fundcode ORDER BY timekey))
FROM     fund_returns f
ORDER BY fundcode,
         timekey

Chris