Hi all,
I am trying to take the example of Expanding Hierarchies from SQL 2000 Books
Online to include quantities. I have changed the BOL example to the analogy
of building a car. Given that you want to build 1 car, how many of each
part is needed to build the complete car. 1 engine with 2 carburetors , 4
wheels, 5 lug nuts per wheel, etc.
** I do need to stick with SQL 2000 unfortunately. **

IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'Hierarchy')
DROP Table dbo.Hierarchy
GO
CREATE TABLE Hierarchy
(Parent VARCHAR(20) NOT NULL,
Child VARCHAR(20),
qty int
CONSTRAINT UIX_ParentChild
UNIQUE NONCLUSTERED (Parent,Child))

CREATE CLUSTERED INDEX CIX_Parent
ON Hierarchy(Parent)
GO


INSERT Hierarchy VALUES('Car','Engine', 1)
INSERT Hierarchy VALUES('Car','Wheel', 4)
INSERT Hierarchy VALUES('Engine','Piston', 4)
INSERT Hierarchy VALUES('Piston','Ring', 2)
INSERT Hierarchy VALUES('Wheel','Lug Nut', 5)
INSERT Hierarchy VALUES('Wheel','Hub Cap', 1)
INSERT Hierarchy VALUES('Lug Nut','Washer', 2)
INSERT Hierarchy VALUES('Engine','Carburetor', 2)
INSERT Hierarchy VALUES('Carburetor','Valve', 2)



IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'expand')
DROP proc dbo.expand
GO
CREATE PROCEDURE expand (@current char(20)) AS
SET NOCOUNT ON
DECLARE @lvl int, @line char(60), @qty int, @Parent_qty int
CREATE TABLE #stack (item char(20), lvl int, stack_qty int)
INSERT INTO #stack VALUES (@current, 1, 1)
SELECT @lvl = 1, @Parent_qty = 0
Print ' qty Parent Qty'
Print '-----------------------------------'
WHILE @lvl > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE lvl = @lvl)
BEGIN
SELECT @current = item, @qty = stack_qty
FROM #stack
WHERE lvl = @lvl
SELECT @line = (space(@lvl -1) + @current + cast(@qty as
char(2)) + ' ' + cast(@Parent_qty as char(2)))
PRINT @line
DELETE FROM #stack
WHERE lvl = @lvl
AND item = @current
INSERT #stack
SELECT Child, (@lvl + 1), qty
FROM Hierarchy
WHERE Parent = @current
IF @@ROWCOUNT > 0
SELECT @lvl = (@lvl + 1),
@Parent_qty = @qty --get parent qty
before going down a level
END
ELSE
SELECT @lvl = @lvl - 1
END -- WHILE
GO


EXEC expand 'Car'





--Results:
qty Parent Qty
-----------------------------------
Car 1 0
Wheel 4 1
Hub Cap 1 4
Lug Nut 5 4
Washer 2 5
Engine 1 5
Carburetor 2 1
Valve 2 2
Piston 4 2
Ring 2 4


The Parent_qty seems to work until you have to move back up the hierarchy
level. See 'Engine'. Its parent_qty should be 1 (1 engine per car), not 5.
The desired end result is to show a total qty of each part required. I was
just going to get a total qty by multiplying qty * Parent_qty. I see now
this won't work for something like 'Washer'. Its total for the entire car
should be 40 (2 washers per lug nut, 5 lug nuts per wheel, 4 wheels per
car).


Ideas, thoughts, links to examples where this has already been done???

Thanks very much.
Happy Holidays !!

Hi Artie,

Haven't had much time to work on this but maybe it'll give you an
idea

DECLARE @current VARCHAR(10)
DECLARE @original VARCHAR(10)

SET @original = 'Washer'
SET @current = @original

WHILE EXISTS (SELECT 1 FROM Hierarchy WHERE Child = @current)
BEGIN
SET @current = (SELECT TOP 1 Parent
FROM Hierarchy
WHERE Child = @current)
PRINT @current
END

DECLARE @count INT
SET @count = 1

WHILE @original <> @current
BEGIN
SET @count = @count * (SELECT qty FROM Hierarchy WHERE Child =
@original)
PRINT @original + ' - ' + CAST(@count AS VARCHAR)
SET @original = (SELECT Parent FROM Hierarchy WHERE Child =
@original)
END

Good luck!
J

>> Ideas, thoughts, links to examples where this has already been done? <<

Get a copy TREES & HIERARCHY IN SQL and look at the Nested Sets model
for BOM. It will be much easier than what you are doing.

Artie (artie2269@yahoo.com) writes:
> The Parent_qty seems to work until you have to move back up the
> hierarchy level. See 'Engine'. Its parent_qty should be 1 (1 engine
> per car), not 5.

Obviously, you need to restore @Parent_qty to be for the previous level.

Rather than rewriting the procedure, I offer a different solution,
using a recursive procedure (which has the drawback that it will
not handler more than 32 levels).

CREATE PROCEDURE expand @item varchar(20),
@lvl tinyint = 1,
@qty int = 1,
@parent_qty int = NULL AS

DECLARE @child varchar(20)

IF @lvl = 1
BEGIN
CREATE TABLE #output(rowno int IDENTITY,
lvl tinyint NOT NULL,
item varchar(20) NOT NULL,
qty int NOT NULL,
parent_qty int NULL)
END

INSERT #output(lvl, item, qty, parent_qty)
VALUES (@lvl, @item, @qty, @parent_qty)

SELECT @lvl = @lvl + 1, @parent_qty = @qty

DECLARE cur CURSOR STATIC LOCAL FOR
SELECT Child, qty FROM Hierarchy WHERE Parent = @item
OPEN cur

WHILE 1 = 1
BEGIN
FETCH cur INTO @child, @qty
IF @@fetch_status <> 0
BREAK

EXEC expand @child, @lvl, @qty, @parent_qty
END

DEALLOCATE cur

IF @lvl = 2
BEGIN
SELECT space(lvl) + item, qty, parent_qty
FROM #output
ORDER BY rowno
END
go


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

i second that
its pretty simple and easy to implement. no recursion is a plus


On Dec 19, 12:21 am, --CELKO-- <jcelko...@earthlink.net> wrote:
> >> Ideas, thoughts, links to examples where this has already been done? <<
>
> Get a copy TREES & HIERARCHY IN SQL and look at the Nested Sets model
> for BOM. It will be much easier than what you are doing.