471,306 Members | 953 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,306 software developers and data experts.

analytical functions

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
Expand|Select|Wrap|Line Numbers
  1.  
  2. select a.*,sum((fundreturns+1)*1000) over (partition by fundcode order by timekey) runningtotal from  fund_returns a
  3.  
  4.  
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(multiplication 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
Dec 19 '07 #1
3 1620
amitpatel66
2,367 Expert 2GB
Hi Srinivasan,

Welcome to TSDN!!

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

Thanks
MODERATOR
Dec 19 '07 #2
amitpatel66
2,367 Expert 2GB
I am not sure how you get the product using any analytical function but I have done using pipelined functions as shown below:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SQL> select * from xyz;
  3.  
  4. FC        FR        TK
  5. -- --------- ---------
  6. F1        10       100
  7. F1        20       200
  8. F1        30       300
  9. F1        40       400
  10.  
  11. SQL> select text from user_source where name = 'RMUL';
  12.  
  13. TEXT
  14. ----------------------------------------------------------------------------------------------------
  15. TYPE rmul IS TABLE OF INTEGER;
  16.  
  17. SQL> select text from user_source WHERE name = 'MULT';
  18.  
  19. TEXT
  20. ----------------------------------------------------------------------------------------------------
  21. FUNCTION mult RETURN rmul PIPELINED IS
  22. res NUMBER:= 1;
  23. BEGIN
  24. FOR I IN (SELECT fr+1 fn FROM xyz) LOOP
  25. res:= res * I.fn * 1000;
  26. PIPE ROW(res);
  27. END LOOP;
  28. return;
  29. END;
  30.  
  31. 9 rows selected.
  32.  
  33. SQL> select * FROM TABLE(mult);
  34.  
  35. COLUMN_VALUE
  36. ------------
  37.        11000
  38.    231000000
  39.    7.161E+12
  40.    2.936E+17
  41.  
  42. SQL> 
  43.  
  44.  
I hope this helps!!
Dec 19 '07 #3
Hi Srinivasn,

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

Expand|Select|Wrap|Line Numbers
  1. SELECT   f.fundcode,
  2.          f.fundreturns,
  3.          f.timekey,
  4.          EXP (SUM (LN (f.fundreturns + 1)) OVER (PARTITION BY fundcode ORDER BY timekey))
  5. FROM     fund_returns f
  6. ORDER BY fundcode,
  7.          timekey
Chris
Dec 19 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by elena | last post: by
5 posts views Thread by hokiegal99 | last post: by
99 posts views Thread by David MacQuigg | last post: by
5 posts views Thread by Yuri G. | last post: by
2 posts views Thread by Bryan Olson | last post: by
23 posts views Thread by Timothy Madden | last post: by
7 posts views Thread by Immortal Nephi | last post: by
reply views Thread by rosydwin | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.