I have a situation where I have a lot of dynamic heirarchical data. I work for a manufacturing company, and as such, items we produce have Bills-of Materials (BOM). Some of our BOMs have items on them that have BOMs as well. This nesting can go as deep as 20 levels. Some components on each of the BOMs have a flag set on them.
I need to know the best way to drill through all of these BOMs to get a list of all the items that are flagged.
I tried to sketch out what I'm talking about in the attached image.Originally posted by joshplummer
I have a situation where I have a lot of dynamic heirarchical data. I work for a manufacturing company, and as such, items we produce have Bills-of Materials (BOM). Some of our BOMs have items on them that have BOMs as well. This nesting can go as deep as 20 levels. Some components on each of the BOMs have a flag set on them.
I need to know the best way to drill through all of these BOMs to get a list of all the items that are flagged.
I tried to sketch out what I'm talking about in the attached image.
Can you provide us with DDL and some sample data to play around with ??|||I don't know if I can get exactly what you need. I can describe it further if that would help. It is fairly complex:
Table
-BOM_Master
Fields
-BOM Version [PK] (varchar(20))
-BOM ID [PK] - Is actually an Item # from our Inventory DB (varchar(20))
-BOM Date (Date)
Table
-BOM_Version
Fields
-BOM Version [PK][FK-BOM_Master] (varchar(20))
-BOM Version Date (Date)
Table
-BOM_Detail
Fields
-BOM Version [PK][FK-BOM_Master] (varchar(20))
-BOM ID [PK][FK-BOM_Master] (varchar(20))
-BOM Line [PK] (Int)
-BOM Line Description (varchar(50))
-BOM Flag (bit)
This is a simplified version of our actual tables, and since this is our business system, I shouldn't post DDL or data since the sample data we use is our real data, just in a test environment.|||First I am assuming that all components at each level have the same structure and that the BOM cannot be recursive (ie BOM1 cannot be called at a lower level); this makes logical sense BUT data entry is data entry.
If you create a parent-child relationship on each BOM the level which it is at could become irelevant.
By passing the BOM_ID of the first level you want to search, a recursive query (read CURSOR -- Sorry Brett) can return a recordset (temporary table? - sorry again Brett) including the search level defined as the recurrsion number in the CURSOR. This recordset can be used to build your output. The output would be based on passing the Child_BOM_ID as the parameter of the next query.
I don't have time right now to example the query but I am doing something similar for an organization hierarchy.|||You are correct. A BOM cannot call itself. So, BOM1 cannot have BOM1 as a component.
I thought about the recursive function, but haven't attempted it yet. I have done that before for a location heirarchy. But, for some reason, this seems more complicated, and I can't nail down exactly what to do.|||Transact-SQL Cookbook by Ales Spetic & Jonathan Gennick.
This book has a whole chapter devoted on hierarchies in SQL Server.
Check out www.oreilly.com for more information.
Hope this helps.|||You will need to create a reference table with BOM_ID,Child BOM_ID.
The BOM_ID can be duplicates to include multiple Children. the joint BOM_ID + Child BOM_ID will create a unique record.
When a BOM is added, the subsequent children must be added. Peferably this table should be populated with the subcomponents first then referenced by its parent component.
Running a CURSOR through this table will allow you to build all of the required BOM entries in a temporary table which can then be linked with the master components table to generate your output.
It is important that each "widget" is regarded as a potential parent and a potential child. This should not be any more complex than any other Cursor based query.
The only concren that I have is for the following example:
the inital BOM calls for 3 typeA screws a sublevel BOM calls for 2 more typeA screws.
Do they get reported seperately or within a common pool?|||Unfortunatley, I don't have control over how the data is created. I have to work with the structure that is created by the applicaion, which isn't very friendly with objects outside of it's own little world.|||Josh,
This is ugly but it could work.
The flag is actually irrelevant. The inner join of BOM_Detail to BOM_Master will supercede it (or match it)
It should be possible to do this but you will need to maintain three tables in your cursor:
the accumulated BOMS [ACCUM_BOM_DETAIL]
the BOMS to search on for children [SEARCH_BOM_DETAIL]
the BOMS returned from the search [RETURNED_BOM_DETAIL]
Thus, start with the initial BOM as the search in [SEARCH_BOM_DETAIL]
1 - WITH CURSOR, return all BOMS at the next level (append to [RETURNED_BOM_DETAIL])
2 -when CURSOR EOF ([SEARCH_BOM_DETAIL]) append [SEARCH_BOM_DETAIL] records into [ACCUM_BOM_DETAIL]
3 - clear [SEARCH_BOM_DETAIL]
4 - copy [RETURNED_BOM_DETAIL] into [SEARCH_BOM_DETAIL] (next
5 - clear [RETURNED_BOM_DETAIL]
run steps 1-5 until the SEARCH returns a NULL Recordset.
The result will be the BOM DETAIL for every sub level. You should be abe to manage the inner joins and versioning through your query.
Good Luck
Originally posted by joshplummer
I don't know if I can get exactly what you need. I can describe it further if that would help. It is fairly complex:
Table
-BOM_Master
Fields
-BOM Version [PK] (varchar(20))
-BOM ID [PK] - Is actually an Item # from our Inventory DB (varchar(20))
-BOM Date (Date)
Table
-BOM_Version
Fields
-BOM Version [PK][FK-BOM_Master] (varchar(20))
-BOM Version Date (Date)
Table
-BOM_Detail
Fields
-BOM Version [PK][FK-BOM_Master] (varchar(20))
-BOM ID [PK][FK-BOM_Master] (varchar(20))
-BOM Line [PK] (Int)
-BOM Line Description (varchar(50))
-BOM Flag (bit)
This is a simplified version of our actual tables, and since this is our business system, I shouldn't post DDL or data since the sample data we use is our real data, just in a test environment.|||Could you post some sample code that does what you're suggesting. I don't think I've done anything like that using cursors...
That sounds like a good idea. Any indications of performance? I know cursors can be a little cumbersome.|||Unfortunately I cannot.
Paid by the hour. and don't want to lose my contract. I'll see what I can do tonight but no promises.
Originally posted by joshplummer
Could you post some sample code that does what you're suggesting. I don't think I've done anything like that using cursors...
That sounds like a good idea. Any indications of performance? I know cursors can be a little cumbersome.|||You don't need a cursor to do this (unless you are paid by the hour).
In the BOM_Master table, you say that ID represents an item from you inventory. Is this the value that may also represent another BOM through which you need to drill down?
BOM_Version > BOM_Master > BOM_Detail|||The only issue is the BOM within a BOM within a BOM ...
The query needs to be recursive until there are no more BOMS (i.e. returned recordset is null). I agree that the internal queries will not need to be CURSORS, but to build the n layers of the hierarchy you need to rerun the query.
The biggest problem will be how to handle duplicates. For example a screw is a component of the main BOM and of a level 3 component.
Unfortunately for joshplummer this is nota simple task.
Originally posted by blindman
You don't need a cursor to do this (unless you are paid by the hour).
In the BOM_Master table, you say that ID represents an item from you inventory. Is this the value that may also represent another BOM through which you need to drill down?
BOM_Version > BOM_Master > BOM_Detail|||This can be accomplished by creating a table variable, seeding it with the initial value, and then running this single skeleton SQL statement:
while @.@.Rowcount > 0
insert into TableVariable
select Datatable.ChildData
from DataTable
inner join TableVariable on DataTable.ParentID = TableVariable.ID
where not exists (select ID from TableVariable where TableVariable.ID = Datatable.ChildData)|||I appreciate everyone's help with this. I was able to get the process working using a recursive cursor based stored proc. It's fairly simple, to be honest, and it's very quick. It screams through a 3000+ line BOM in less than a second. And, the results are spot on!
Once again, thanks for the help everyone.|||Good to hear.
Originally posted by joshplummer
I appreciate everyone's help with this. I was able to get the process working using a recursive cursor based stored proc. It's fairly simple, to be honest, and it's very quick. It screams through a 3000+ line BOM in less than a second. And, the results are spot on!
Once again, thanks for the help everyone.|||Cursors are crutches for VB programmers. Learn to use set-based operations.|||Blindman,
I appologize, I didn't see your original posting.
Your statements were correct.
If you have an alternative to the cursor, I'd love to see it.|||Not 100% sure about your table relationships and keys, but here is an example that returns the flagged BOM_Details for a specified BOM, including sub-BOMs:
--------------------
declare @.ParentBOM varchar(20)
declare @.BOM_List table
(BOM_Version varchar(20) Primary Key Clustered)
--First, seed the BOM_List table
insert into @.BOM_List
(BOM_Version)
select BOM_Version
from BOM_Master
where BOM_Version = @.ParentBOM
--This ensures that the BOM actually exists in the BOM_Master table
--Now collect all the sub-records
while @.@.Rowcount > 0 --This executes if the previous statement was successfull.
insert into @.BOM_List
(BOM_Version)
select BOM_Master.BOM_ID --The child record
from BOM_Master
inner join @.BOM_List BOM_List on BOM_Master.BOM_Version = BOM_List.BOM_Version
--This clause ensures that we don't try to insert a BOM more than once
where not exists (select * from @.BOM_List CurrentRecords where CurrentRecords.BOM_Version = BOM_Master.BOM_ID)
--Now you can join your BOM_List table to your details table:
select *
from BOM_Detail
inner join @.BOM_List BOM_List on BOM_Detail.BOM_Version = BOM_List.BOM_Version
where BOM_Detail.Flag = 1
--------------------
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment