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

[Former Member]

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

[former_member186095]

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,