Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2002
    Posts
    33

    Unanswered: BOM structure using SQL

    Hi there,
    Im trying to create a multi-level BOM using MS SQL.
    Here is my Table structure:
    Table 1:
    Part-Number
    Table 2:
    Part-Number
    Part-Owner

    So if I link Part-number from T1 to Part-owner I get all parts for that level.
    If I link back from T2 using Part-number to T1 I can link again to T2 and check if there are any lower level part structures.
    This imbedded link has no limits but usually does not go more then 6-7 levels.
    Can anyone help with setting up code for this BOM?

    Thanks

  2. #2
    Join Date
    Oct 2002
    Posts
    369
    Your table structure looks valid for logically modeling a hierarchal system. The join you described sounds valid as well.

    You might want to add a Where clause and use that in a criteria driven stored procedure that will generate a BOM explosion out of a select, given an assembly SetID; (to return a specific sub part listing result set for a setID representing a desired 'assembly'). Im not sure I understand what you are asking for help with, if you'd like, post an email to me with more specifics, and perhaps I can send you some examples that would help.

    Maps
    INNER JOIN
    Sets
    ON Maps.SetID = Sets.SetID
    INNER JOIN
    Sets Sets_1 ON Maps.SubID = Sets_1.SetID
    WHERE
    Sets.SetID = [desired SetID]

    NOTE:
    Sets = "Table 1"
    TABLE [Sets] (
    [SetID],
    [SetName])

    Maps = "Table 2"
    TABLE [Maps] (
    [SetID],
    [SubID])

  3. #3
    Join Date
    Nov 2002
    Posts
    33

    Need help to understand the solution...

    Hi,
    I think you went over my head here
    My SQL exposure is somewhat limited. I have not used stored procedures and it sounds from your explanation that I should be using them.
    The logic I was going to use was to link Table1 to Table2, then Table2 to Table1 using the other part, however, at some point that becomes imposable.
    If the stored procedure is the way to go, can you elaborate a little more how to set it up and what it does?

    Thanks much

  4. #4
    Join Date
    Oct 2002
    Posts
    369

    Arrow

    RE: If the stored procedure is the way to go, can you elaborate a little more how to set it up and what it does?

    Q1[How one may use a stored proc that accepts a parameter?]
    A1 OK, I'll try to give you a short example here. If you'd like, post an email to me, and I can send you more complete demo examples.

    Example using proc hr_Set_SubSets:
    Assume you wish to see a BOM Explosion for a part / assembly number; say for part / assembly number 387. A stored proc that accepts an Int parameter and uses it as criteria on a properly constructed Select statement (built on the Maps and Sets tables) can do just that (assuming the Maps and Sets tables are correctly populated with data). Executing the example proc hr_Set_SubSets proc with 387 assigned to its parameter, would then return all 'components' for the part / assembly with a SetID = 387.

    -- Example use of a stored proc hr_Set_SubSets:
    Declare
    @vi int
    select @vi = 387
    Exec dbo.hr_Set_SubSets @pSetID = @vi

    -- Creating the stored proc hr_Set_SubSets in the example:
    CREATE Proc dbo.hr_Set_SubSets
    (@pSetID int)
    AS
    SELECT
    dbo.Sets.SetName AS SetName,
    dbo.Sets.SetID As SetID,
    Sets_1.SetName AS SubSetName,
    dbo.Maps.SubID As SubSetID
    FROM
    dbo.Maps
    INNER JOIN
    dbo.Sets
    ON
    dbo.Maps.SetID = dbo.Sets.SetID
    INNER JOIN
    dbo.Sets Sets_1
    ON
    dbo.Maps.SubID = Sets_1.SetID
    WHERE
    dbo.Sets.SetID = @pSetID

    Maps and Sets schemas:
    TABLE [Sets] (
    [SetID],
    [SetName])

    TABLE [Maps] (
    [MapID],
    [SetID],
    [SubID])

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •