Checking of parent item

[Former Member]

Experts,

If I fetch items from OITM table, how to check if the item is having a parent item or not in BOM? Then again tht parent item is having parent or not...etc

[Former Member]

Hi Akash,

This Item have BOM

SELECT T0.[ItemCode], T0.[ItemName], T0.[TreeType] FROM OITM T0 WHERE T0.[TreeType] = 'P'

This item not have BOM

SELECT T0.[ItemCode], T0.[ItemName], T0.[TreeType] FROM OITM T0 WHERE T0.[TreeType] = 'N'

Thanks,

Srujal Patel

Edited by: Srujal Patel 77 on Jun 14, 2011 6:54 AM

[Former Member]

Create Procedure ParentCheck

@itemcode varchar (50),

@count1 int

As

Begin

SET @count1= (select count (T1.Code)from OITM T0 INNER JOIN ITT1 T1 ON T0.ItemCode = T1.Code where T1.Code = 'Child1')

select @count1

if (@count1<>0)

End

I am trying to create a query like above...user will give itemcode as parameter, if that item has parent, it will show parent info,

and it will go on in a loop.....

please help

[former_member206488]

Hi Akash,

Try the query mentioned in your thread:

you seems posted simlar thread twice.

Thanks,

Neetu

[Former Member]

Hi champ,

Try this one

SELECT T0.[Code], T2.[ItemName], T0.[TreeType], T1.[Code], T1.[Quantity], T1.[Warehouse] FROM OITT T0 INNER JOIN ITT1 T1 ON T0.Code = T1.Father INNER JOIN OITM T2 ON T0.Code = T2.ItemCode WHERE T1.[Code] = [%0]

regards,

Vignesh

[Former Member]

While @Next<=ISNULL((select COUNT (Father) from @temp), 0)

Begin

select @fathercode = Father from @temp where (select ROW_NUMBER() over (order by Father)from @temp = @Next)

select T1.Father , T1.Code from ITT1 T1 where T1.Code=@fathercode

set @Next = @Next + 1

End

insert into @temp select T1.Father from ITT1 T1 where T1.Code = @fathercode

select @fathercode = Father from @temp where (select ROW_NUMBER() over (order by Father)from @temp = @Next)

this line gives error...How can we get the row id in SQL server and put it in a condition?

plz help

[former_member206488]

Check http://wiki.sdn.sap.com/wiki/display/B1/SAPB1SQLI-PRComponentswithinBillsofMaterialforAnyorAll link.

Thanks,

Neetu