Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Recursive query for a Bill of Materials table Expand / Collapse
Author
Message
Posted Friday, November 9, 2007 10:10 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:22 PM
Points: 7,074, Visits: 15,318
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?
Post #420761
Posted Friday, November 9, 2007 10:37 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:13 AM
Points: 35,262, Visits: 31,745
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #420763
Posted Friday, November 9, 2007 10:40 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:06 PM
Points: 1,563, Visits: 2,392
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.
Post #420764
Posted Sunday, November 11, 2007 1:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 5, 2009 1:57 AM
Points: 6, 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.
Post #420856
Posted Sunday, November 11, 2007 5:18 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:13 AM
Points: 35,262, Visits: 31,745
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #420867
Posted Monday, November 12, 2007 7:00 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:06 PM
Points: 1,563, Visits: 2,392
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.
Post #421029
Posted Monday, November 12, 2007 9:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 5, 2009 1:57 AM
Points: 6, 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?
Post #421117
Posted Monday, November 12, 2007 9:44 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:06 PM
Points: 1,563, Visits: 2,392
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.
Post #421120
Posted Tuesday, November 13, 2007 2:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 5, 2009 1:57 AM
Points: 6, Visits: 20
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.

Post #421412
Posted Tuesday, November 13, 2007 7:05 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:06 PM
Points: 1,563, Visits: 2,392
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

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.
Post #421566
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse