Re: How to code SQL for a BOM Explosion?

Glenn,
indeed not the most elegant approach.
You should use only one table, and let each record (optionally) refer to the primary key of the table. See the famous emplyee table for an example. (a pig's ear in an entity relationship diagram). Then you can use PRIOR and CONNECT BY. Hope this helps,

Frans Hovenkamp

Glenn Heinze heeft geschreven in bericht <7pgpmh$lgt$1_at_cougar.golden.net>...
>If I have a table of finished goods named Product like this:
>
>ProductID Name
>======== ====
>1 Product A
>2 Product B
>3 Product C
>4 Product D
>
>and a components table like this:
>
>ProductID PartID Count
>======== ===== =====
>1 2 1
>1 3 2
>3 4 3
>
>I would like to report that "Product A" is composed of 1 piece of "Product
>B" and 6 pieces of "Product D" (ie 2 pieces of "Product C" each of which is
>itself composed of 3 pieces of "Product D".
>
>There is no fixed depth to the recursion.
>
>Is there a way to write a SELECT statement for this where a can avoid
>procedures? Is this where I could use the "connect by ... prior..."
clause?
>Is there a way to do it with "standard" sql? Or perhaps the two table
>structure of "Product" and "Component" is not the most elegant approach?
>
>Thanks in advance for any help
>-Glenn
>
>
Received on Fri Aug 20 1999 - 06:09:52 CDT