Leszek J. Leszek J. - 4 months ago 19
SQL Question

Production BoM explosion drill through phantom BoM

This is my first post, although Stack Overflow helped me numerous times so far. Hope this time will be same way.

I have tried to build recurring query and UDF, using sources and code found online, but with no luck. Just got more and more confused.

What I have:


  • Items table - with Item No and BoM No

  • BoMLines table - with Bom No and all sub Items No OR phantom BoM No's






What I'm after:

Basically I need to list of all items with all of their sub items and quantity per.
Every thing seemed to be OK, until I found out we also have phantom BoMs(Bom's calling other BoMs) and those have to be 'drilled in' to item level.




Result expected:
Screenshot

Which is table with list of all items with BoM explode to next item!!





To show easier what I'm after DDL included:

-- Drop table Items if exist
IF OBJECT_ID('test.Items') IS NOT NULL
DROP TABLE test.Items;

-- Create Items table
CREATE TABLE test.Items (
ItemID varchar(30) COLLATE Latin1_General_CI_AS NOT NULL,
Description varchar(100) COLLATE Latin1_General_CI_AS NOT NULL,
Prod_BOM_No varchar(30) COLLATE Latin1_General_CI_AS NULL,
PRIMARY KEY CLUSTERED (ItemID)
WITH (
PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
ON [PRIMARY]

-- Drop table BOMLines if exist
IF OBJECT_ID('test.BOMLines') IS NOT NULL
DROP TABLE test.BOMLines;

-- Create BOMLines table
CREATE TABLE test.BOMLines (
AutoID int IDENTITY(1, 1) NOT NULL,
Prod_BOM_No varchar(30) COLLATE Latin1_General_CI_AS NOT NULL,
Type tinyint DEFAULT 1 NOT NULL,
No_ varchar(30) COLLATE Latin1_General_CI_AS NOT NULL,
Qty_Per decimal(18, 8) DEFAULT 1.5 NULL,
PRIMARY KEY CLUSTERED (AutoID)
WITH (
PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
ON [PRIMARY]


And also DML to show example of data I work on:

--Insert Test Items to Items table
INSERT INTO
test.Items
(ItemID, Description, Prod_BOM_No)
VALUES
('I1001', 'Item I1001', 'BM1010'), ('I1002', 'Item I1002', 'BM1020'), ('I1003', 'Item I1003', 'BM1030'),
('I1004', 'Item I1004', 'BM1040'), ('I1005', 'Item I1005', 'BM1050'), ('I1006', 'Item I1006', 'BM1060'),
('I1007', 'Item I1007', 'BM1070'), ('I1008', 'Item I1008', 'BM1080'), ('I1009', 'Item I1009', 'BM1090'),
('I1010', 'Item I1010', 'BM1100'), ('I5001', 'Item I5001', ''), ('I5002', 'Item I5002', ''),
('I5003', 'Item I5003', ''), ('I5004', 'Item I5004', ''), ('I5005', 'Item I5005', ''),
('I5006', 'Item I5006', ''), ('I5007', 'Item I5007', ''), ('I5008', 'Item I5008', ''),
('I5009', 'Item I5009', ''), ('I5010', 'Item I5010', ''), ('I5011', 'Item I5011', ''),
('I5012', 'Item I5012', ''), ('I5013', 'Item I5013', ''), ('I5014', 'Item I5014', ''),
('I5015', 'Item I5015', ''), ('I5016', 'Item I5016', ''), ('I5017', 'Item I5017', ''),
('I5018', 'Item I5018', ''), ('I5019', 'Item I5019', ''), ('I5020', 'Item I5020', '');

-- Insert test BOMs to BOMLines table
INSERT INTO
test.BOMLines
( Prod_BOM_No, No_, [Type])
VALUES
('BM1010', 'I5001', 1), ('BM1020', 'I5002', 1), ('BM1020', 'I5003', 1),
('BM1030', 'BM1031', 2), ('BM1030', 'I5004', 1), ('BM1031', 'I5005', 1),
('BM1040', 'I5006', 1), ('BM1050', 'I5007', 1), ('BM1050', 'BM1051', 2),
('BM1051', 'BM1052', 2), ('BM1052', 'I5008', 1), ('BM1060', 'I5009', 1),
('BM1060', 'I5010', 1), ('BM1060', 'I5011', 1), ('BM1060', 'BM1061', 2),
('BM1061', 'I5012', 1), ('BM1061', 'I5013', 1), ('BM1061', 'BM1062', 2),
('BM1062', 'I5013', 1), ('BM1062', 'I5014', 1), ('BM1070', 'I5015', 1),
('BM1080', 'I5016', 1), ('BM1080', 'I5017', 1), ('BM1080', 'I5018', 1),
('BM1090', 'I5019', 1), ('BM1100', 'I5020', 1);

Answer

You can use a Recursive Common Table Expression:

;With BOMs (Prod_BOM_No, Type, No_, Qty_Per) AS
(
    select Prod_BOM_No, Type, No_, Qty_Per
    from test.BOMLines
    union all
    select test.BOMLines.Prod_BOM_No, BOMs.Type, BOMs.No_, convert(decimal(18, 8), test.BOMLines.Qty_Per * BOMs.Qty_Per) as Qty_Per
    from test.BOMLines
    join BOMs 
    on test.BOMLines.Type = 2 and test.BOMLines.No_ = BOMs.Prod_BOM_No
)
select ItemID, Description, No_, Qty_Per
from test.Items
join BOMs
on test.Items.Prod_BOM_No = BOMs.Prod_BOM_No
    and Type = 1
order by 1, 2
Comments