SQL not fetching all the affected items in a BOM. Query
While trying to get the bom details from an Eco using below criteria, the result gives only those affected items which r having bom . I need all the affected items including bom if they have.but unfortunately the result is not showing the items having no bom.
FROM change c,
page_two p2change,
item i,
rev r,
bom b,
page_two p2bom,
page_two p2item
WHERE c.change_number IN (‘TestEco’)
AND c.id =r.change
AND i.id =r.item
AND c.id =p2change.ID
AND i.id =b.item(+)
AND to_number(b.flags)=0
AND b.component =p2bom.id
AND i.id =p2item.id;
Hi Pradeep,
Below query worked for me. please give a try
select distinct i.item_number
FROM change c,
page_two p2change,
item i,
rev r,
bom b,
page_two p2bom,
page_two p2item
WHERE c.change_number IN (‘ECO-41393’)
AND c.id =r.change
AND i.id =r.item
AND c.id =p2change.ID
AND i.id =b.item(+)
–AND to_number(b.flags)=0
AND b.component =p2bom.id(+)
AND i.id =p2item.id;
Regards,
Anand.
