Re: [firebird-support] nesting - weight calculation
On 29/01/2015 12:29, 'checkmail' check_m...@satron.de [firebird-support] wrote: Is there a simpler statement possible than my stored procedure (recursive) Oooh, it's the transitive closure of sparse connectivity matrix problem again. (With, in this case, the arcs of the directed graph being weighted.) Same problem as given this table of people, find all the ancestors/descendants of such-and-such. Or, in other words, SQL isn't that good at tree structures, you have to get a bit clever. (The other main thing I've found, over the decades, that SQL is not very good at being time series.) -- Tim Ward
RE: [firebird-support] nesting - weight calculation
there is a table with all Material (stock), each one has a number. Now, in a second table there is listed the material that is a part of the Material. For example: Material ABC Material BCD Material XXX Material OMV Material KOM Material OMV can be a Part of XXX and XXX a Part of ABC, of course the BCD can assigned to XXX, OMV, ABC. Now I would like to know what is the total weight of the main material, (total weight from abc + amount of bcd + amount of xxx ABC has 2 XXX, XXX has 2 OMV Now we have 1 X ABC, 2 times XXX and 4 X OMV, OMV weighs 0,5kg and so on The structure Table Mainmaterial PartNo Description Table Material PartNo of Mainmaterial PartNoA = assigned Material Amount (of PartNoA) Is there a simpler statement possible than my stored procedure (recursive) While, I don't see any reference to a weight field for the material... my answer would be: I don't see how you can avoid the SP, just minimize the number of times you use it. Store the weight in new Part_Weight table, trigger to tag TableMaterial changes (Changed = True) when change any of the sub-parts are made, and then a SP (which would be called before you run any analysis/report, and/or run on a scheduled basis) to recalculate the weights of any Part that have changed sub-parts. In this way you have the weights and only do the recursive SP when absolutely necessary. Sean
Re: [firebird-support] nesting - weight calculation
There is at least a good alternative, Frank Ingermann showed how to do something very similar when calculating the amount of ingredients required when baking a marble cake at a Firebird conference a few years ago. He used a recursive CTE. Whether a recursive CTE is simpler than a recursive procedure or not depends partly on implementation, partly on what you mean by simple and partly on who you ask. I liked Franks solution. Set __ Hello, there is a table with all Material (stock), each one has a number. Now, in a second table there is listed the material that is a part of the Material. For example: Material ABC Material BCD Material XXX Material OMV Material KOM Material OMV can be a Part of XXX and XXX a Part of ABC, of course the BCD can assigned to XXX, OMV, ABC. Now I would like to know what is the total weight of the main material, (total weight from abc + amount of bcd + amount of xxx ABC has 2 XXX, XXX has 2 OMV Now we have 1 X ABC, 2 times XXX and 4 X OMV, OMV weighs 0,5kg and so on The structure Table Mainmaterial PartNo Description Table Material PartNo of Mainmaterial PartNoA = assigned Material Amount (of PartNoA) Is there a simpler statement possible than my stored procedure (recursive) Thank you.
