MySQL Forums
Forum List  »  Stored Procedures

Re: Avoiding cursors
Posted by: james
Date: February 01, 2006 12:09AM

Yes that is similar to what I ended up with, except that balancewsubs wasnt being calculated correctly.

It turns out in order to get it right I have to sum(a2.balancewsubs) where a2.id = p.account in a subquery because
"a2.balancewsubs = if(a1.id=a2.id,a2.balancewsubs, a2.balancewsubs+ p.amount)"
adds p.amount onto the original a2.balancewsubs figure (which is always 0 because it is reset at the beginning of the sub) rather than a culmulative total as it goes through the table.

Problem being that I cant use the same table in a sub query. So eventually I have a statement after it to calculate the balancewsubs and insert it into another table. Then another statement that takes the balancewsubs out of the temporary table and puts it back in the correct place.

It works fast (about 3 seconds instead of the original 25) but if anyone knows of a way to get round this "same table sub query limit" please let me know.

Kind regards

James

Options: ReplyQuote


Subject
Views
Written By
Posted
1961
January 27, 2006 11:47PM
1323
January 28, 2006 08:03AM
1213
January 29, 2006 08:22AM
1234
January 29, 2006 02:26PM
1158
January 29, 2006 10:46PM
Re: Avoiding cursors
1427
February 01, 2006 12:09AM
1312
February 01, 2006 04:02PM
1287
January 30, 2006 06:27AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.