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)
>
>
> Regards,
> Ole

It looks like you want a list of all the leaf nodes then. In 10g there is a pseudo column called connect_by_isleaf that you could use to test for this. I have not got a 10g install to test it out however. The link to the documentation is here:-

http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10759/pseudocolumns001.htm#sthref671 Received on Thu Sep 29 2005 - 07:05:30 CDT