Query for BOM

[inna_baskina]

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

[inna_baskina]

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