SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Recursive query for a Bill of Materials table


Recursive query for a Bill of Materials table

Author
Message
Matt Miller (4)
Matt Miller (4)
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12171 Visits: 18572
If it's a process THIS expensive, you should consider making this a permanent table. Meaning - keep the existing table just as it is, and have another to track the atomic parts manifest.

I can't imagine that your sub assembly parts change all that often and the properly designed stored proc could "recalculate" only those items with affected assemblies/sub-assemblies.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85450 Visits: 41079
Matt Miller (11/9/2007)
If it's a process THIS expensive, you should consider making this a permanent table. Meaning - keep the existing table just as it is, and have another to track the atomic parts manifest.

I can't imagine that your sub assembly parts change all that often and the properly designed stored proc could "recalculate" only those items with affected assemblies/sub-assemblies.


I absolutely agree! And, if you're going to do that, make lookups fast! Joe Celko's "Nested Set" hierarchies are the berries!

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Greg Snidow
Greg Snidow
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2115 Visits: 2486
Jeff Moden (11/9/2007)
Matt Miller (11/9/2007)
If it's a process THIS expensive, you should consider making this a permanent table. Meaning - keep the existing table just as it is, and have another to track the atomic parts manifest.

I can't imagine that your sub assembly parts change all that often and the properly designed stored proc could "recalculate" only those items with affected assemblies/sub-assemblies.


I absolutely agree! And, if you're going to do that, make lookups fast! Joe Celko's "Nested Set" hierarchies are the berries!


Yep...I guess that's why they have things like that, so you don't do what I did.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
andy.dawson
andy.dawson
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 20
My apologies Greg. I'd made my earlier example simpler and forgot to amend on my second post.

I seem to have caused quite a fuss with this post! I dunno if am asking the impossible or just being incredibly bad at wording what I am trying to achieve!!!!?!?!??

In the least confusing sentence possible:

I want to display, on screen only, all parts with the description 'S/A of PCB'. Be it in the top level of the BOM or anywhere 'underneath' that level, given the example extract from the system I am using.

In response to the comments regarding how often the Bill of Materials change, unfortunately, we operate on quite a regular bespoke set of BOMs. I extract the information each week to enable planning of PCB build. Headache I know, but that's the business. Sad
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85450 Visits: 41079
You didn't listen to me... lookup "expanding hierarchies" in Books Online and adapt it. It WILL do as you ask.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Greg Snidow
Greg Snidow
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2115 Visits: 2486
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.
andy.dawson
andy.dawson
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 20
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?
Greg Snidow
Greg Snidow
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2115 Visits: 2486
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.
andy.dawson
andy.dawson
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 20
I might have to bail out on this one! Sad

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.
Greg Snidow
Greg Snidow
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2115 Visits: 2486
andy.dawson (11/13/2007)
I might have to bail out on this one! Sad

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

http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.access.adp.sqlserver&cat=en_US_fcdda3c1-4861-428d-be18-b0d9a39f74b7&lang=en&cr=US

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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search