Are you talking about inventory or manufacturing BOMs?
I have this very rudimentary query I built long time ago, but it is still good for inventory boms five level deep.
SELECT B.ITEMNMBR [BILL No.], I.ITEMDESC [BILL Desc.], Q.QTYONHND [BILL QOH], B.CMPTITNM COMPONENT, I2.ITEMDESC [COMPONENT DESC], Q2.QTYONHND [COMP QOH],SUB1, [SUB1 DESC], [SUB1 QOH], SUB2, [SUB2 DESC], [SUB2 QOH], SUB3, [SUB3 DESC], [SUB3 QOH], SUB4, [SUB4 DESC], [SUB4 QOH]
FROM BM00111 B
LEFT JOIN
(
SELECT C.ITEMNMBR, C.CMPTITNM SUB1, I3.ITEMDESC [SUB1 DESC], Q3.QTYONHND [SUB1 QOH], SUB2, [SUB2 DESC], [SUB2 QOH], SUB3, [SUB3 DESC], [SUB3 QOH], SUB4, [SUB4 DESC], [SUB4 QOH]
FROM BM00111 C
LEFT JOIN
(
SELECT S.ITEMNMBR, S.CMPTITNM SUB2, I4.ITEMDESC [SUB2 DESC], Q4.QTYONHND [SUB2 QOH], SUB3, [SUB3 DESC], [SUB3 QOH], SUB4, [SUB4 DESC], [SUB4 QOH]
FROM BM00111 S
LEFT JOIN
(
SELECT S1.ITEMNMBR, S1.CMPTITNM SUB3, I5.ITEMDESC [SUB3 DESC], Q5.QTYONHND [SUB3 QOH], SUB4, [SUB4 DESC], [SUB4 QOH]
FROM BM00111 S1
LEFT JOIN
(SELECT S2.ITEMNMBR, S2.CMPTITNM SUB4, I6.ITEMDESC [SUB4 DESC], Q6.QTYONHND [SUB4 QOH]
FROM BM00111 S2
INNER JOIN IV00101 I6
ON S2.CMPTITNM = I6.ITEMNMBR
JOIN IV00102 Q6
ON S2.CMPTITNM = Q6.ITEMNMBR AND Q6.LOCNCODE =''
) S2
ON S2.ITEMNMBR = S1.CMPTITNM
INNER JOIN IV00101 I5
ON S1.CMPTITNM = I5.ITEMNMBR
JOIN IV00102 Q5
ON S1.CMPTITNM = Q5.ITEMNMBR AND Q5.LOCNCODE =''
) S1
ON S1.ITEMNMBR = S.CMPTITNM
INNER JOIN IV00101 I4
ON S.CMPTITNM = I4.ITEMNMBR
JOIN IV00102 Q4
ON S.CMPTITNM = Q4.ITEMNMBR AND Q4.LOCNCODE =''
) S
ON S.ITEMNMBR = C.CMPTITNM
INNER JOIN IV00101 I3
ON C.CMPTITNM = I3.ITEMNMBR
JOIN IV00102 Q3
ON C.CMPTITNM = Q3.ITEMNMBR AND Q3.LOCNCODE =''
) C
ON B.CMPTITNM = C.ITEMNMBR
INNER JOIN IV00101 I
ON B.ITEMNMBR = I.ITEMNMBR
JOIN IV00102 Q2
ON B.CMPTITNM = Q2.ITEMNMBR AND Q2.LOCNCODE =''
INNER JOIN IV00101 I2
ON B.CMPTITNM = I2.ITEMNMBR
JOIN IV00102 Q
ON B.ITEMNMBR = Q.ITEMNMBR AND Q.LOCNCODE =''
WHERE I.ITMCLSCD NOT LIKE 'FIN%' AND B.BILL_STATUS = 1 AND I.ITMCLSCD !='BAD'
