Re: SQL-statement - BOM problem

ohahaahr_at_hotmail.com wrote:
> Hi !
>
> I am working a bill-of-material (BOM) problem, and i need some help.
> I want to make a report for my employees at the warehouse, telling them
> which articles to pick.
>
> I have the following table BOMTABLE:
>
> PARENT CHILD QTY
> ------ ------ ---
> bike wheel 2
> bike saddle 1
> car wheel 4
> car door 4
> wheel tire 1
> wheel rim 1
>
>
> To see the materials needed to build a bike, I have launched the
> following statement:
>
> SELECT bom.child , LEVEL
> FROM bomtable bom
> START WITH bom.parent = 'bike'
> CONNECT BY PRIOR child = parent
>
>
> This gives me the following output:
>
> CHILD LEVEL
> ------ -----
> saddle 1
> wheel 1
> tire 2
> rim 2
>
>
> But the people at the warehouse needs a report like this, showing the
> bottom level of the BOM, and the QTY to pick.
>
> CHILD QTY
> ------ ---
> saddle 1
> tire 2
> rim 2
>
>
> (They should not pick 2 wheels, but 2 tires and 2 rims).
>
>
> Is there an easy way to do this ?
>
> (I am using Oracle 10g)

Lookup "hierarchical total" on AskTom. Received on Thu Sep 29 2005 - 14:01:13 CDT