SQL-statement - BOM problem

From: <ohahaahr_at_hotmail.com>
Date: 29 Sep 2005 01:06:39 -0700
Message-ID: <1127981199.064366.49570@z14g2000cwz.googlegroups.com>

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 Received on Thu Sep 29 2005 - 03:06:39 CDT