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

Author | Svein Erling Tysvær |

Post date | 2015-01-29T22:44:28Z |

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.

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.