MySQL Forums
Forum List  »  Stored Procedures

Problem with recursive query
Posted by: Markus Manninger
Date: January 25, 2013 12:34PM

I do have a problem that needs to solved recursively. As far as I know it's possible to call procedures recuresively (setting the max_sp_recursion_depth in cfg) but no functions. Am I right?
Now I have to solve a problem where invoices are subtracted from others which can have other "sub invoices" to be substracted, and so on. Ok, here is how my DB suctruce looks like:

CREATE TABLE `invoice` (
`amount` decimal(8,0) NOT NULL DEFAULT '0',

CREATE TABLE `subtraction` (
`invoice_id` int(11) NOT NULL,
`subtract_id` int(11) NOT NULL

And here is example data:

INSERT INTO `invoice` VALUES (1,200),(2,500),(3,1200),(4,3400);
INSERT INTO `subtraction` VALUES (2,1),(3,2),(3,1),(4,3),(4,2),(4,1);

In real the amount of the invoice is an other table which will be summed up, but thats no problem. In this example I have four invoices. The First (id=1) has no sub-invoice to subctract, so the total amount is just the amount, which is 200. The second invoice has amount 500 minus 200, the first invoice, which is 300. Now it's getting tricky. From the third invoice (id=3) the total amount of invoice 1 and 2 has to be substracted:
1200 - ( 500 - 200 ) - 200 = 700

The same with invoice 4, where 3,2 and 1 has to be substracted recusively:
3400 - ( 1200 - ( 500 - 200 ) - 200 ) - ( 500 - 200 ) - 200 = 2200

I hope you understand my problem and what's the idea. I can limit the maximum recursion depth to 12 (or a little more) which stands for 12 month of a year to prevent infinite loops. And it would be quite easy if I could use functions for the calculation.
So does anyone ever had to solve a similar problem or has an idea?


Edited 1 time(s). Last edit at 01/25/2013 12:35PM by Markus Manninger.

Options: ReplyQuote

Written By
Problem with recursive query
January 25, 2013 12:34PM
January 26, 2013 12:55PM

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.