MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Avoiding the cursor

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
seekaysk
Beginner


Joined: 31 Aug 2007
Posts: 49
Topics: 15

PostPosted: Tue Mar 17, 2009 12:29 pm    Post subject: Avoiding the cursor Reply with quote

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
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose

PostPosted: Tue Mar 17, 2009 1:14 pm    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail Visit poster's website
seekaysk
Beginner


Joined: 31 Aug 2007
Posts: 49
Topics: 15

PostPosted: Tue Mar 17, 2009 1:21 pm    Post subject: Reply with quote

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
View user's profile Send private message
jsharon1248
Intermediate


Joined: 08 Aug 2007
Posts: 291
Topics: 2
Location: Chicago

PostPosted: Tue Mar 17, 2009 1:34 pm    Post subject: Reply with quote

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
View user's profile Send private message
seekaysk
Beginner


Joined: 31 Aug 2007
Posts: 49
Topics: 15

PostPosted: Tue Mar 17, 2009 1:54 pm    Post subject: Reply with quote

thanks jsharon1248.
_________________
Thanks.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose

PostPosted: Tue Mar 17, 2009 7:59 pm    Post subject: Reply with quote

seekaysk,

Do you have any other column in the same table which is unique to every record? Question
Back to top
View user's profile Send private message Send e-mail Visit poster's website
seekaysk
Beginner


Joined: 31 Aug 2007
Posts: 49
Topics: 15

PostPosted: Wed Mar 18, 2009 12:45 pm    Post subject: Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group