SQL query for BOM as displayed on UI
Looking to pass parent item number and parent rev to a query to obtain the level 1 BOM and revisions of the level 1 components as shown on the UI.
I have reviewed a few suggestions here and excellent responses by Kevin: https://myagileplm.com/question/db-query-to-show-level1-bom-of-a-item/ however I am missing the component revision in this query.
I tried with your suggestion similar to obtaining P2 and P3 fields however I am looking for the Revision and combining with the Rev table as below did not yield much success đŸ™‚
select b.item_number, b.find_number, b.quantity, b.id,b.item,b.component,b.change_in,b.change_out, r.rev_number
from agile.bom b, agile.rev r
where b.change_out = 0 and b.item = (select id from agile.item where item_number='TEST999')
and exists (select null from agile.change where id = b.change_in and statustype in (3,4) )
and b.component=r.item and r.change=b.change_out;
I believe this is because you are connecting r.change = b.change_out when you’ve already specified that b.change_out = 0.
Change the last part of your query to b.change_in. If your organization also uses sites, you may have to specify a site ID, or at the very least add the following to your where clause:
and b.site = r.site
Hope that helps.
