Subject | RE: [firebird-support] nesting - weight calculation |
---|---|

Author | Leyne, Sean |

Post date | 2015-01-29T16:05:04Z |

> there is a table with all Material (stock), each one has a number. Now, in aWhile, I don't see any reference to a weight field for the material... my answer would be:

> 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)

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