View previous topic :: View next topic |
Author |
Message |
seekaysk Beginner
Joined: 31 Aug 2007 Posts: 49 Topics: 15
|
Posted: Tue Mar 17, 2009 12:29 pm Post subject: Avoiding the cursor |
|
|
Hey guys,
I have to apply a formula to one columns of values. I was trying to think if I could avoid a cursor and just do it within a select or may be something like that...
details: Table1 contains 1 column whose values are some decimals:
Code: |
0.012345
1.236700
3.458766
0.000112
...
...
|
formula to be applied is:
Code: |
calculated first row value = database first row value
calculated second row value =
((1 + calculated first row value) * (1 + database second row value)) - 1
calculated third row value =
((1 + calculated second row value) * (1 + database third row value)) - 1
...
...
|
Simple way to do it is through cursor. Select the column in cursor. open the cursor. For each fetch apply the formula. store the calculated value for next row....and that's it.
I just want to know if there is any other way, say a simple select and applying this complex formula (which also depends on the previous row's calculated value) or may be any other way ?
fyi. I did not search for this problem on mvsforums search as I was sure I would not be able to find this. (May be I am wrong...but...)
thanks for your reading and replying. _________________ Thanks. |
|
Back to top |
|
 |
kolusu Site Admin

Joined: 26 Nov 2002 Posts: 12378 Topics: 75 Location: San Jose
|
Posted: Tue Mar 17, 2009 1:14 pm Post subject: |
|
|
seekaysk,
What is the final Output from this? Do you need the final computed value only? I understood it as just running total formula. Do you have any other column that you are selecting? or this is just a stand alone column?
Kolusu |
|
Back to top |
|
 |
seekaysk Beginner
Joined: 31 Aug 2007 Posts: 49 Topics: 15
|
Posted: Tue Mar 17, 2009 1:21 pm Post subject: |
|
|
Thanks kolusu for repying.
fyi. It is only one standalone column that I need to select in my cursor or standalone select statement. And all I need is the only final computed value. _________________ Thanks. |
|
Back to top |
|
 |
jsharon1248 Intermediate
Joined: 08 Aug 2007 Posts: 291 Topics: 2 Location: Chicago
|
Posted: Tue Mar 17, 2009 1:34 pm Post subject: |
|
|
Since the description of your project is dependent on a results set concept (1st row, 2nd row, etc), you need a cursor at some point. You can get fancy and utilize DECLAREd GLOBAL TEMP TABLES or UDF's or Stored Procs, but ultimately, you need the cursor because it's part of the requirement. |
|
Back to top |
|
 |
seekaysk Beginner
Joined: 31 Aug 2007 Posts: 49 Topics: 15
|
Posted: Tue Mar 17, 2009 1:54 pm Post subject: |
|
|
thanks jsharon1248. _________________ Thanks. |
|
Back to top |
|
 |
kolusu Site Admin

Joined: 26 Nov 2002 Posts: 12378 Topics: 75 Location: San Jose
|
Posted: Tue Mar 17, 2009 7:59 pm Post subject: |
|
|
seekaysk,
Do you have any other column in the same table which is unique to every record?  |
|
Back to top |
|
 |
seekaysk Beginner
Joined: 31 Aug 2007 Posts: 49 Topics: 15
|
Posted: Wed Mar 18, 2009 12:45 pm Post subject: |
|
|
yes. I do have. It would be the set of two columns. One is: "unique id" column (PIC S9(9) COMP) and other is date column. This combination of two columns is unique. _________________ Thanks. |
|
Back to top |
|
 |
|
|