-1

I'm using DB2 V7 and i have a product table like below :

select * from product;

Product |Component|Quantity |Line number|
--------+---------+---------+------------
Product1|Part1    |1        |1
Product1|Product2 |2        |2    
Product1|Part2    |1        |3
Product2|Part3    |4        |1
Product2|Part4    |4        |2
Product9|Part8    |5        |1

What I want is to perform a SELECT that return all child components that compose a product. The desired Query should return a result like below..

Product |Component|Quantity|Line number|BOM Level
--------+---------+--------+-----------+---------
Product1|Part1    |1       |1          |1
Product1|Part3    |8       |2          |2
Product1|Part4    |8       |3          |2
Product1|Part2    |1       |4          |1
Product2|Part3    |4       |1          |1
Product2|Part4    |4       |2          |1
Product9|Part8    |5       |1          |1
  • What is "Line Number"? Where the value of 4 in resultset is taken from? – Akina Oct 23 at 10:16
  • Does the maximal nesting level is not specified? – Akina Oct 23 at 10:23
  • Three to four level should be enough.. – user2253258 Oct 23 at 10:46
  • 4 stand for the position of the component in the product – user2253258 Oct 23 at 10:49
  • Three to four level should be enough If four levels max you may use simple 4-part union. 4 stand for the position of the component in the product I.e. it is simple ROW_NUMBER()? – Akina Oct 23 at 10:50
1

In MySQL the below query gives correct result it seems. Try to adapt...

WITH RECURSIVE
cte (sProduct, sComponent, sQuantity, Product, Component, Quantity, HaveChilds) 
AS ( SELECT Product, 
            Component, 
            Quantity, 
            Product, 
            Component, 
            Quantity,
            EXISTS ( SELECT 1
                     FROM product p2
                     WHERE p1.Component = p2.Product )
     FROM product p1
     WHERE NOT EXISTS ( SELECT 1
                        FROM product p3
                        WHERE p1.Product = p3.Component )
  UNION ALL
     SELECT cte.sProduct, 
            cte.sComponent, 
            cte.sQuantity, 
            p1.Product, 
            p1.Component, 
            cte.Quantity * p1.Quantity,
            EXISTS ( SELECT 1
                     FROM product p2
                     WHERE p1.Component = p2.Product )
     FROM cte, product p1
     WHERE cte.Component = p1.Product
       AND cte.HaveChilds = 1 
)
SELECT sProduct Product, 
       Component, 
       Quantity, 
       ROW_NUMBER() OVER (PARTITION BY sProduct 
                          ORDER BY Component) Line_Number
FROM cte
WHERE HaveChilds = 0
ORDER BY 1, 2;

fiddle (source data is slightly changed, 3rd level added).

This fiddle while using DB2 (and removing RECURSIVE keyword) gives empty result (maybe DB2 requires different approaches to working with boolean values?).

Not the answer you're looking for? Browse other questions tagged or ask your own question.