on 09-01-2008 2:22 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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,
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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,
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
91 | |
8 | |
7 | |
4 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.