Re: How to code SQL for a BOM Explosion?

Glenn Heinze <glenn.heinze_at_city.kitchener.on.ca> wrote in message news: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

IMHO, it can not be done using a primitive hierarchical SQL statement, since you can not generate a "full path" or related the leaf to its root in Oracle's hierarachical query.
You may use a function to implement it. Received on Sat Aug 21 1999 - 03:57:52 CDT