Bill of material (SQL2000)

Hi,
Does anyone got a really good/fast example on how to do a BOM with unlimited
levels in SQL2000
- without using cursors :)
2 tables (one for items, one for parent/child relations)
In SQL2005 we of course are blessed with the new CTE, not in SQL2000 :-/
In advance, thanks...
Kr. Sorenhow do you want your data output? just a SP that returns a list of all
materials a particular item requires, right down the tree?
if so then the only way I can think of without cursors is using global
temporary tables and recursively called stored procedures to populate
it (but then you might as well use a cursor for that).
if you're wanting columns of data to represent the tree structure then
it's Dynamic SQL you'll need|||Soren, here goes:
-- DDL & Sample Data for Parts, BOM
SET NOCOUNT ON;
USE tempdb;
GO
IF OBJECT_ID('dbo.BOM') IS NOT NULL
DROP TABLE dbo.BOM;
GO
IF OBJECT_ID('dbo.Parts') IS NOT NULL
DROP TABLE dbo.Parts;
GO
CREATE TABLE dbo.Parts
(
partid INT NOT NULL PRIMARY KEY,
partname VARCHAR(25) NOT NULL
);
INSERT INTO dbo.Parts(partid, partname) VALUES( 1, 'Black Tea');
INSERT INTO dbo.Parts(partid, partname) VALUES( 2, 'White Tea');
INSERT INTO dbo.Parts(partid, partname) VALUES( 3, 'Latte');
INSERT INTO dbo.Parts(partid, partname) VALUES( 4, 'Espresso');
INSERT INTO dbo.Parts(partid, partname) VALUES( 5, 'Double Espresso');
INSERT INTO dbo.Parts(partid, partname) VALUES( 6, 'Cup Cover');
INSERT INTO dbo.Parts(partid, partname) VALUES( 7, 'Regular Cup');
INSERT INTO dbo.Parts(partid, partname) VALUES( 8, 'Stirrer');
INSERT INTO dbo.Parts(partid, partname) VALUES( 9, 'Espresso Cup');
INSERT INTO dbo.Parts(partid, partname) VALUES(10, 'Tea Shot');
INSERT INTO dbo.Parts(partid, partname) VALUES(11, 'Milk');
INSERT INTO dbo.Parts(partid, partname) VALUES(12, 'Coffee Shot');
INSERT INTO dbo.Parts(partid, partname) VALUES(13, 'Tea Leaves');
INSERT INTO dbo.Parts(partid, partname) VALUES(14, 'Water');
INSERT INTO dbo.Parts(partid, partname) VALUES(15, 'Sugar Bag');
INSERT INTO dbo.Parts(partid, partname) VALUES(16, 'Ground Coffee');
INSERT INTO dbo.Parts(partid, partname) VALUES(17, 'Coffee Beans');
CREATE TABLE dbo.BOM
(
partid INT NOT NULL REFERENCES dbo.Parts,
assemblyid INT NULL REFERENCES dbo.Parts,
unit VARCHAR(3) NOT NULL,
qty DECIMAL(8, 2) NOT NULL,
UNIQUE(partid, assemblyid),
CHECK (partid <> assemblyid)
);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 1, NULL, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 2, NULL, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 3, NULL, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 4, NULL, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 5, NULL, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 6, 1, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 7, 1, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(10, 1, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(14, 1, 'mL', 230.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 6, 2, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 7, 2, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(10, 2, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(14, 2, 'mL', 205.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(11, 2, 'mL', 25.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 6, 3, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 7, 3, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(11, 3, 'mL', 225.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(12, 3, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 9, 4, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(12, 4, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 9, 5, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(12, 5, 'EA', 2.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(13, 10, 'g' , 5.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(14, 10, 'mL', 20.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(14, 12, 'mL', 20.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(16, 12, 'g' , 15.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(17, 16, 'g' , 15.00);
GO
-- Creation Script for Function fn_partsexplosion
---
-- Function: fn_partsexplosion, Parts Explosion
--
-- Input : @.root INT: Root part id
--
-- Output : @.PartsExplosion Table:
-- id and level of contained parts of input part
-- in all levels
--
-- Process : * Insert into @.PartsExplosion row of input root part
-- * In a loop, while previous insert loaded more than 0 rows
-- insert into @.PartsExplosion next level of parts
---
USE tempdb;
GO
IF OBJECT_ID('dbo.fn_partsexplosion') IS NOT NULL
DROP FUNCTION dbo.fn_partsexplosion;
GO
CREATE FUNCTION dbo.fn_partsexplosion(@.root AS INT)
RETURNS @.PartsExplosion Table
(
partid INT NOT NULL,
qty DECIMAL(8, 2) NOT NULL,
unit VARCHAR(3) NOT NULL,
lvl INT NOT NULL,
n INT NOT NULL IDENTITY, -- surrogate key
UNIQUE CLUSTERED(lvl, n) -- Index will be used to filter lvl
)
AS
BEGIN
DECLARE @.lvl AS INT;
SET @.lvl = 0; -- Initialize level counter with 0
-- Insert root node to @.PartsExplosion
INSERT INTO @.PartsExplosion(partid, qty, unit, lvl)
SELECT partid, qty, unit, @.lvl
FROM dbo.BOM
WHERE partid = @.root;
WHILE @.@.rowcount > 0 -- while previous level had rows
BEGIN
SET @.lvl = @.lvl + 1; -- Increment level counter
-- Insert next level of subordinates to @.PartsExplosion
INSERT INTO @.PartsExplosion(partid, qty, unit, lvl)
SELECT C.partid, P.qty * C.qty, C.unit, @.lvl
FROM @.PartsExplosion AS P -- P = Parent
JOIN dbo.BOM AS C -- C = Child
ON P.lvl = @.lvl - 1 -- Filter parents from previous level
AND C.assemblyid = P.partid;
END
RETURN;
END
GO
-- Parts Explosion
SELECT P.partid, P.partname, PE.qty, PE.unit, PE.lvl
FROM dbo.fn_partsexplosion(2) AS PE
JOIN dbo.Parts AS P
ON P.partid = PE.partid;
Output:
partid partname qtyn unit lvl
-- -- -- -- --
2 White Tea 1.00n EA 0
6 Cup Cover 1.00n EA 1
7 Regular Cup 1.00n EA 1
10 Tea Shot 1.00n EA 1
14 Water 205.00n mL 1
11 Milk 25.00n mL 1
13 Tea Leaves 5.00n g 2
14 Water 20.00n mL 2
-- Parts Explosion, Aggregating Parts
SELECT P.partid, P.partname, PES.qty, PES.unit
FROM (SELECT partid, unit, SUM(qty) AS qty
FROM dbo.fn_partsexplosion(2) AS PE
GROUP BY partid, unit) AS PES
JOIN dbo.Parts AS P
ON P.partid = PES.partid;
Output:
partid partname qty unit
-- -- -- --
2 White Tea 1.00 EA
6 Cup Cover 1.00 EA
7 Regular Cup 1.00 EA
10 Tea Shot 1.00 EA
13 Tea Leaves 5.00 g
11 Milk 25.00 mL
14 Water 225.00 mL
In SQL Server 2005 it would look like this:
-- CTE Solution for Parts Explosion
DECLARE @.root AS INT;
SET @.root = 2;
WITH PartsExplosionCTE
AS
(
-- Anchor member returns root part
SELECT partid, qty, unit, 0 AS lvl
FROM dbo.BOM
WHERE partid = @.root
UNION ALL
-- Recursive member returns next level of parts
SELECT C.partid, CAST(P.qty * C.qty AS DECIMAL(8, 2)),
C.unit, P.lvl + 1
FROM PartsExplosionCTE AS P
JOIN dbo.BOM AS C
ON C.assemblyid = P.partid
)
SELECT P.partid, P.partname, PE.qty, PE.unit, PE.lvl
FROM PartsExplosionCTE AS PE
JOIN dbo.Parts AS P
ON P.partid = PE.partid;
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"Soren S. Jorgensen" <nospam@.nodomain.com> wrote in message
news:ObORLLiXGHA.3936@.TK2MSFTNGP05.phx.gbl...
> Hi,
> Does anyone got a really good/fast example on how to do a BOM with
> unlimited levels in SQL2000
> - without using cursors :)
> 2 tables (one for items, one for parent/child relations)
> In SQL2005 we of course are blessed with the new CTE, not in SQL2000 :-/
> In advance, thanks...
> Kr. Soren
>
>|||Get a copy of TREES & HIERARCHIES IN SQL for details then Google Nested
Sets model.