Skip to Content
0
Former Member
Nov 08, 2011 at 07:18 PM

BOM Queries

23 Views

SELECT T0.ItemCode, T1.Code, T1.Quantity 
FROM ITT1 T1
INNER JOIN OITM T0 ON T1.FATHER = T0.ITEMCODE AND T0.QryGroup50 = 'Y' 
WHERE T1.Code Not In (SELECT Code From OITT) AND T1.Code Not Like '55%'
Union ALL
SELECT T0.ItemCode, T2.Code, T2.Quantity 
FROM ITT1 T1
INNER JOIN OITM T0 ON T1.FATHER = T0.ITEMCODE AND T0.QryGroup50 = 'Y'
INNER JOIN ITT1 T2 ON T2.FATHER = T1.CODE AND T1.CODE NOT LIKE '45A%'
WHERE T2.Code Not In (SELECT Code From OITT) AND T2.Code Not Like '55%'
Union ALL
SELECT T0.ItemCode, T3.Code, T3.Quantity 
FROM ITT1 T1
INNER JOIN OITM T0 ON T1.FATHER = T0.ITEMCODE AND T0.QryGroup50 = 'Y'
INNER JOIN ITT1 T2 ON T2.FATHER = T1.CODE AND T1.CODE NOT LIKE '45A%'
INNER JOIN ITT1 T3 ON T3.FATHER = T2.CODE AND T2.CODE NOT LIKE '45A%'
WHERE T3.Code Not In (SELECT Code From OITT) AND T3.Code Not Like '55%'
Union ALL
SELECT T0.ItemCode, T4.Code, T4.Quantity 
FROM ITT1 T1
INNER JOIN OITM T0 ON T1.FATHER = T0.ITEMCODE AND T0.QryGroup50 = 'Y'
INNER JOIN ITT1 T2 ON T2.FATHER = T1.CODE AND T1.CODE NOT LIKE '45A%'
INNER JOIN ITT1 T3 ON T3.FATHER = T2.CODE AND T2.CODE NOT LIKE '45A%'
INNER JOIN ITT1 T4 ON T4.FATHER = T3.CODE AND T3.CODE NOT LIKE '45A%'
WHERE T4.Code Not In (SELECT Code From OITT) AND T4.Code Not Like '55%'

Above is the query I used to display father items, BOM components (& all their related sub components) and quantities. I noticed that it does not display correctly for sub components quantities.

For example, BOM component A has QTY of 3. It further contains another 3 sub-components (A1, A2, A3) which each have QTY of 1 respectively. The query shows A1, A2, A3 each has QTY of 1. Ideally, I would like to have them show QTY of 3 (upper component qty of 3 multiple sub component qty of 1). How can I modify the query to show the result I wanted? Thanks.