Skip navigation links

MySQL Forums :: Stored Procedures :: Problem with recursive query


Advanced Search

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

Hi,
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` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`amount` decimal(8,0) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM;

CREATE TABLE `subtraction` (
`invoice_id` int(11) NOT NULL,
`subtract_id` int(11) NOT NULL
) ENGINE=MyISAM;

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?

thanks,
max



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

Options: ReplyQuote


Subject Views Written By Posted
Problem with recursive query 1811 Markus Manninger 01/25/2013 12:34PM
Re: Problem with recursive query 560 Rick James 01/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.