Indented Bill of Materials

From: Robert Schuldenfrei (schuldenfrei_at_comcast.net)
Date: 02/01/05 

Date: Tue, 1 Feb 2005 14:19:17 -0500

Dear NG,

After some poking around the Internet and reading Joe Celko's book on Trees
and Hierarchies, I have made some progress getting a Product Structure into
an indented Bill of Materials (BOM). Steve Kass demonstrated this in the
program segments listed below. In order to move forward from here I need a
better understanding of how this works and why some of my changes fail. I
am new to SQL Server, so my issues are of the most basic kind. Once I
really know why this works in detail, I would like to make some improvements
to better fit my application. I will also need to work efficiently with
trees if I am ever going to produce enterprise level applications. HOWEVER,
first I need to learn how this works.

This code builds the table called "Hi". My guess is that it is called Hi
because it is going to build hierarchies.

--TreeData.sql

USE MCS3
DROP TABLE Hi
create table Hi (
  PageID int,
  ParentPageID int,
  Descrip varchar(50)
)

insert into Hi values (1,null,'500-000')
insert into Hi values (2,1,'500-100')
insert into Hi values (3,1,'500-006')
insert into Hi values (4,1,'500-004')
insert into Hi values (5,1,'500-005')
insert into Hi values (6,1,'500-001')
insert into Hi values (7,2,'500-003')
insert into Hi values (8,2,'500-002')
insert into Hi values (9,4,'500-080')
INSERT INTO Hi VALUES (10,5,'500-080')
insert into Hi values (11,null,'800-000')
insert into Hi values (12,11,'800-004')
insert into Hi values (13,11,'500-001')
insert into Hi values (14,11,'800-100')
insert into Hi values (15,11,'800-006')
insert into Hi values (16,11,'800-005')
insert into Hi values (17,12,'500-080')
insert into Hi values (18,14,'800-002')
insert into Hi values (19,14,'500-003')
INSERT INTO Hi VALUES (20,16,'500-080')

SELECT * FROM Hi

--------------------------------------------------------------------------------------------

Now that the table has been built with two product structures (500-000 and
800-000) you can run the following code to produce the indented BOM:

--TreeSpan.sql

DROP TABLE #temp

SELECT *, --get all of the columns from
Hi
   0 as Level, --new column in #Temp.
What does 0 do?
   CAST(str(pageid,10) as varchar(8000))
   AS H --create hierarchy string
   INTO #Temp --into temp table
   FROM Hi --source original table
   WHERE ParentPageID is null --get the root of each tree first

DECLARE @lev int
SET @lev = 0 --initialize current level to
0

WHILE @@rowcount > 0 --what causes this to <= 0 in order to
end?
   BEGIN
      SET @lev = @lev + 1 --increase current level
      INSERT INTO #Temp --construct rows in #Temp. Error w/new
col
      SELECT G.*, --all columns from Hi
         @lev, --current level
         T.H+str(G.pageid,10) --path to this node
         FROM Hi G JOIN #Temp T --this JOIN is the key. How does it
work?
         ON G.ParentPageID = T.PageID
            AND T.level = @lev - 1
   END

SELECT space(level*3)+descrip AS bom, --output indented BOM
   PageID
   FROM #temp
   ORDER by H

------------------------Output from SELECT
above-----------------------------------
  bom PageID
500-000 1
   500-100 2
      500-003 7
      500-002 8
   500-006 3
   500-004 4
      500-080 9
   500-005 5
      500-080 10
   500-001 6
800-000 11
   800-004 12
      500-080 17
   500-001 13
   800-100 14
      800-002 18
      500-003 19
   800-006 15
   800-005 16
      500-080 20

--------------------------------------------------------------------------------------

As you can see the program works. If anyone can clear up my questions in
the comments I would be very greatful. I tried to add another column called
qty_per_assembly to the Hi table. I got an error at line 19 of the program
that is INSERT INTO #Temp. The error was: Insert Error: Column name or
number supplied values does not match table definition. How does #Temp get
defined? Well, that is enough for now.

Thanks in advance,

Bob 

-- 
Robert Schuldenfrei
S. I. Inc.
32 Ridley Road
Dedham, MA  02026
bob@s-i-inc.com
781/329-4828

Relevant Pages
Quantcast