November 12, 2007 at 7:00 am
Jeff Moden (11/11/2007)
You didn't listen to me... lookup "expanding hierarchies" in Books Online and adapt it. It WILL do as you ask.
Jeff, once again you were exactly correct. Not that I doubted you, but I had to see for myself. I had a terrible time understanding what the procedure was doing, since the BOL description does not have an example. Anyhow, Andy, I think this will work for you. At least it worked for me with the sample .xls you gave. Its simply a very small adaptation of the BOL example suited for your data. It is assuming you dump your BOM file into a table called BOM. Sorry for the lack of comments, but I do not really understand it myself, but only fooled with it until it produced the desired results.
ALTER PROCEDURE expand (@current char(20)) as
SET NOCOUNT ON
DECLARE @level int, @line char(20)
CREATE TABLE #stack (item char(20), level int)
CREATE TABLE #BOM (Field1 varchar(200)) --Added this to hold @current
INSERT INTO #stack VALUES (@current, 1)
SELECT @level = 1
WHILE @level > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE level = @level)
BEGIN
SELECT @current = item
FROM #stack
WHERE level = @level
SELECT @line = space(@level - 1) + @current
--PRINT @line + 'line' --dashed this out, did not want to see it
INSERT INTO #BOM SELECT @Current
DELETE FROM #stack
WHERE level = @level
AND item = @current
INSERT #stack
SELECT Field3,@level + 1
FROM BOM
WHERE Field1 = @current
AND SUBSTRING(Field6,1,3) = 'S/A'
AND Field2 IN ('M','P')
IF @@ROWCOUNT > 0
SELECT @level = @level + 1
END
ELSE
SELECT @level = @level - 1
END -- WHILE
SELECT
b1.Field1,
b2.Field2,
b2.Field3,
b2.Field5,
b2.Field6
FROM #BOM b1,
BOM b2
WHERE b1.field1 = b2.field1
AND SUBSTRING(b2.field6,1,10) = 'S/A OF PCB'
Then try EXEC expand 'TX5922.AA'
Greg
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
November 12, 2007 at 9:34 am
Thanks for taking the time to have a closer look.
When I use your code I get an error:
"Error 208: Invalid object name 'expand'"
Any ideas?
November 12, 2007 at 9:44 am
andy.dawson (11/12/2007)
Thanks for taking the time to have a closer look.When I use your code I get an error:
"Error 208: Invalid object name 'expand'"
Any ideas?
Sorry, you need to change the 'ALTER' to 'CREATE' in the first line.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
November 13, 2007 at 2:59 am
I might have to bail out on this one! 🙁
Even if this did exactly what I wanted, I wouldn't be able to administer it because I just 'don't get it' (yet).
I reckon MS Access is more my level at the moment.
Thanks all for your help though, it's much appreciated.
November 13, 2007 at 7:05 am
andy.dawson (11/13/2007)
I might have to bail out on this one! 🙁Even if this did exactly what I wanted, I wouldn't be able to administer it because I just 'don't get it' (yet).
I reckon MS Access is more my level at the moment.
Thanks all for your help though, it's much appreciated.
Andy, I would hate to see you give up that easily. And remember this; anything you can do in Access you can do in SQL Server and then some. The code I posted comes from Books Online, and is a way to expand a heirarchy. The code within the code block will create the procedure and store it as an object on your server, just like you would create a query in Access. The execute statement will produce the *exact* results you specified. The only catch is that you need to have permissions to create a procedure. Its just like Access. You can also use Access as a front end to SQL Server. I have plenty of forms that do just as you need to do. You enter in your parameter, hit a button, and voila, your lines appear. If you do decide you are going to do your entire project in Access, I would suggest AccessMonster.com as your best resource. If you are going to use an Access ADP(specifically made for use with SQL Server, and what I use) you can go to
Now, you said you just don't get it(yet)...me neither buddy, but thanks to your post I now know what a heirarchy is. The great thing about forums like this are that there are plenty of folks who will stick with you until your issue is resolved. This one in particular is a little over my head too, but not for the main contributors here. Two years ago I could not even spell Access. I did not even know what SQL or a database was. Thanks to these fine folks I can keep my head above water at least. Anyhow, good luck.
Greg
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
November 13, 2007 at 7:40 am
Guess I'm just gonna have to document that proc from BOL... 😛
Andy, what the max number of parts a given assembly can have? There's a reason why I ask... two different methods... one's easier to understand although a bit slower and more limited in scope.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 13, 2007 at 7:48 am
Ha ha. You guys aren't gonna let me give up are ya! 😛
There is no real limit to the number of sub assemblies, but I would be confident to say that there would never be more than 6 on anything we produce.
November 13, 2007 at 8:11 am
Jeff Moden (11/13/2007)
Guess I'm just gonna have to document that proc from BOL... 😛Andy, what the max number of parts a given assembly can have? There's a reason why I ask... two different methods... one's easier to understand although a bit slower and more limited in scope.
Oh boy...you know I'm salivating here...
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
November 14, 2007 at 7:33 am
I'm familiar with the BOM (hierarchical) structure. The assumption is that you already have a Parent/Child relationship. With that, you can navigate up and down the hierarchy.
See my posting 366402 at:
http://www.sqlservercentral.com/Forums/Topic366130-8-1.aspx#bm366402
What I've found that works very well for SQL Server is a UDF function per Microsoft KB article 248915.
November 15, 2007 at 5:31 am
That KB article is what you'll find in BOL under "Expanding Hierarchies"...
--Jeff Moden
Change is inevitable... Change for the better is not.
May 4, 2009 at 7:55 am
Hate to bump an old thread, but it is the google result I found for BOM recursive query.
I wound up using your example on that other thread Jeff, http://www.sqlservercentral.com/Forums/Topic366130-8-1.aspx#bm366402
The format winds up being very similar to the way a recursive CTE would be setup, except for my problem temporary table solution is 9x faster, literally. The webpage I call the query from went from 90 secs down to 7.
if you dont want to follow another link, here is example I worked off
Here's an example... you can't do unlimited hierarchies, but it's normally good enough. And, it's not your specific example... it for something I did for someone else... thought it might provide a decent example, though...
--===== If the temporary demonstration tables exist, drop them
IF OBJECT_ID('TempDB..#MyHead') IS NOT NULL
DROP TABLE #MyHead
IF OBJECT_ID('TempDB..#Hierarchy') IS NOT NULL
DROP TABLE #Hierarchy
--===== Create the table to hold the data posted for test
CREATE TABLE #MyHead
(
QuestTreeUID INT PRIMARY KEY CLUSTERED,
ShortDesc VARCHAR(20),
LongDesc VARCHAR(20),
ParentUID INT,
QuestNodeUID INT
)
--===== Populate the table with the test data posted
INSERT INTO #MyHead(QuestTreeUID,ShortDesc,LongDesc,ParentUID,QuestNodeUID)
SELECT 14,'Smartwaste','Smartwaste',0,1 UNION ALL
SELECT 15,'Skip','Skip',14,3 UNION ALL
SELECT 991,'Product','Product',14,2 UNION ALL
SELECT 17,'Material','Material',991,3 UNION ALL
SELECT 18,'Product','Product',991,3 UNION ALL
SELECT 19,'Feedback','Feedback',14,2 UNION ALL
SELECT 20,'Origin of waste','Origin of waste',19,3 UNION ALL
SELECT 21,'Cause','Cause',19,3
--===== Test setup complete, we're ready to rock!
--===== Create and preset a level counter.
DECLARE @CurrentLevel INT
SET @CurrentLevel = 0
--===== Create the Hierarchy table
CREATE TABLE #Hierarchy
(
QuestTreeUID INT PRIMARY KEY,
ParentUID INT,
Level INT,
Hierarchy VARCHAR(8000),
QuestNodeUID INT
)
--===== Seed the Hierarchy table with the top level
INSERT INTO #Hierarchy
(QuestTreeUID,ParentUID,Level,Hierarchy,QuestNodeUID)
SELECT QuestTreeUID,
ParentUID,
0 AS Level,
STR(QuestTreeUID,7)+' ' AS Hierarchy,
QuestNodeUID
FROM #MyHead
WHERE ParentUID =0
--===== Determine the rest of the hierarchy
WHILE @@ROWCOUNT > 0
BEGIN
SET @CurrentLevel = @CurrentLevel + 1 --Started at 0
INSERT INTO #Hierarchy
(QuestTreeUID,ParentUID,Level,Hierarchy,QuestNodeUID)
SELECT p.QuestTreeUID,
p.ParentUID,
@CurrentLevel AS Level,
h.Hierarchy + STR(p.QuestTreeUID,7)+' ' AS Hierarchy,
p.QuestNodeUID
FROM #MyHead p
INNER JOIN #Hierarchy h
ON p.ParentUID = h.QuestTreeUID
AND h.Level = @CurrentLevel - 1
END
--===== Produce the hierarchical report
SELECT p.QuestTreeUID,p.ParentUID,REPLICATE('-----',h.Level)+SPACE(SIGN(h.Level))+ p.ShortDesc
FROM #MyHead p,
#Hierarchy h
WHERE NOT (h.Level = 1 AND h.QuestNodeUID = 3) --Skips out of line entries
AND p.QuestTreeUID = h.QuestTreeUID
ORDER BY h.Hierarchy
select * from #hierarchy
Thanks for the help Jeff.
May 4, 2009 at 11:40 am
New uses of something "old" is never a bore. Thank you for the feedback even on old threads.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 31, 2011 at 10:14 am
Sorry again to reopen an old thread.
I'm using SQL Server 2000. Thus CTE is not available.
I need to expand BOM's.
The BOL feature on Expanding Hierarchies is great. And it's nearly what I need to do.
However, in my task we don't have a single top level structure quite like "World...".
We have parent parts, lots of them, and each parent part breaks down into several child components. Like the BOL example, each child component can break down into further lower level child components.
So using the BOL example I can give it one specific parent part (passed in parm @current) and it will explode it into all its child components. The trouble is I have a table of hundreds of thousands of parent parts that all need exploding and all the child components output altogether into a single table.
Any tips on how to wrap the BOL Example for each occurence of a parent part in my input table within the single stored procedure?
Regards,
Dave
March 31, 2011 at 2:11 pm
Hi Dave,
You really don't want to use the "expanding hierarchy" example in BOL. There's a fairly easy way to replace the CTE using SQL Server 2000 technology.
Is there any chance that you could provide some readily consumable data (please see the first link in my signature line below for how to do that) so that I can demonstrate what I mean with code?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 1, 2011 at 3:16 am
Many thanks for the reply Jeff. I'll work on a test routine as per your first link in your signature block.
With work pressure it could take me a few days to put it together.
(I'm intrigued you say not to use the BOL example, because it very nearly works for me.... and equally intrigued about what you have up your sleeve 🙂
Thanks!
Dave
Viewing 15 posts - 16 through 30 (of 45 total)
You must be logged in to reply to this topic. Login to reply