cancel
Showing results for 
Search instead for 
Did you mean: 

Query for BOM

inna_baskina
Explorer
0 Kudos

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

all its parent items, for example the Table ITT1 consists of following rows

child_item parent_item

-


I1 P1

I1 P2

P1 P3

P2 P4

I1

the result query should display

child_item parent_item1 parent_item2 parent_item3

-


I1 P1 P3

I1 P2 P4

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

Hi Inna

This query not give you exactly what you need, but can give you an idea of who to get the result you want.

select S0.ItemCode, S0.ItemName, isnull(sum(S5.Quantity * S6.AvgPrice), isnull(sum(S2.Quantity * S3.AvgPrice), S0.AvgPrice)) 'Costo del artículo'

from OITM S0 left join OITT S1 on S0.ItemCode = S1.Code

left join ITT1 S2 on S1.Code = S2.Father

left join OITM S3 on S2.Code = S3.ItemCode

left join OITT S4 on S3.ItemCode = S4.Code

left join ITT1 S5 on S4.Code = S5.Father

left join OITM S6 on S5.Code = S6.ItemCode

group by S0.ItemCode, S0.ItemName, S0.AvgPrice

order by S0.ItemCode

Hope this help you

Slds.,

Esteban

former_member186095
Active Contributor
0 Kudos

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. I tested in B1 2007A version and it worked.

Rgds,

0 Kudos

Dear Inna,

here there is a first quick query that you can try. Please, let me know if it is ok for you.

SELECT T0.Father, T0.Code FROM ITT1 T0 WHERE T0.Code = 'CD-RW'

Showing the parents horizontally

child_item parent_item1 parent_item2 parent_item3

it is convenient because you risk to have a long list and to have to scroll to your right to see the parent items.

With my query you will have the list vertically and you can click on the arrow to check the production order.

do you have a particular reason why you want the list from left to right instead of creating it from top to bottom?

Kind Regards,

Marcella Rivi

SAP Business One Forums Team

Kind Regards,