Skip to Content

Query for BOM

Hi All,

I need a query which displays for an spicific BOM child item

all its parent items in the hierarchical structure

For example: the Table ITT1 consists of following rows

child_item parent_item

-


I1 P1

I1 P2

P1 P3

P2 P4

I1 P5

the query I need should display

child_item parent_item1 parent_item2

-


I1 P1 P3

I1 P2 P4

I1 P5

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

3 Answers

  • Sep 01, 2008 at 01:48 PM

    Hi,

    here is the query :

    
    SELECT T1.[Code] "Child_Item", T1.[Father] "Parent_item1", 
    Parent_item2 = (SELECT T2.[FATHER] FROM
     ITT1 T2 WHERE (T2.FATHER = 'P3' and t2.code = t1.father) 
    or (T2.FATHER = 'P4' and t2.code = t1.father))  FROM OITT 
    T0  INNER JOIN ITT1 T1 ON T0.Code = T1.Father 
    WHERE T1.[Code]  = 'i1' 
    
    

    let me know if it is not work well.

    Rgds,

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Sep 02, 2008 at 09:34 PM

    Hi Inna,

    Try this one:

    SELECT distinct T0.Code, T1.Code 'Parent Item1', 
    T1.Father 'Parent Item2'
    FROM dbo.ITT1 T0
    LEFT JOIN dbo.ITT1 T1 ON T0.FATHER = T1.CODE
    WHERE T0.Code  = '[%0]'
    ORDER BY T0.CODE, T1.CODE
    

    Thanks,

    Gordon

    Add comment
    10|10000 characters needed characters exceeded

  • Sep 03, 2008 at 06:44 AM

    Hi All,

    thank you for the quick answers, but I have already found the solution, how to display for an spisific item all it's parent items in all assembly levels.

    It can be done with recursive function calls:

    CREATE FUNCTION dbo.GetParentItems(@ChildItem nvarchar(20))

    RETURNS @retParentItems TABLE(ChildItem nvarchar(20), ParentItem nvarchar(20))

    AS

    BEGIN

    DECLARE @Item nvarchar(20), @ParentItem nvarchar(20)

    DECLARE RetrieveParentItems CURSOR STATIC LOCAL FOR

    SELECT Code, Father FROM ITT1 WHERE Code=@ChildItem

    OPEN RetrieveParentItems

    FETCH NEXT FROM RetrieveParentItems

    INTO @Item, @ParentItem

    WHILE (@@FETCH_STATUS = 0)

    BEGIN

    INSERT INTO @retParentItems

    SELECT * FROM dbo.GetParentItems(@ParentItem)

    INSERT INTO @retParentItems

    VALUES(@Item,@ParentItem)

    -- set @Level = @Level + 1

    FETCH NEXT FROM RetrieveParentItems

    INTO @Item, @ParentItem

    END

    CLOSE RetrieveParentItems

    DEALLOCATE RetrieveParentItems

    RETURN

    END

    GO

    Add comment
    10|10000 characters needed characters exceeded