Product part Query (BOM) [closed]

Question

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

Answer 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?).