> 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
