Home

How to retrieve Orphan Parts from Agile 9.3.3 DB ?

We are using Agile 9.3.3 . Tried with below queries , none of which didnt work.

FIRST QUERY: Not giving any results even though there are Orphan Parts .

Found this query answered to another similar question like mine.

SELECT i.item_number,i.description, rr.rev_number FROM item I JOIN released_rev rr ON i.id = rr.item WHERE i.class = 10000 AND i.item_number NOT IN (SELECT bom.item_number FROM bom) AND i.id NOT IN (SELECT bom.item FROM bom); ===========================================

SECOND QUERY: This query works in 9.3.4 and 9.3.6 . But does not give any result in 9.3.3. Not sure what the issue is.

select i.item_number from item i where i.id not in(select component from bom);

I know the work around would be to use SDK, but i would really like to know why the queries didnt work.

 

Thanks , Priya

Agile User Asked on October 20, 2020 in Agile PLM (v9).

priya_ms

Please find the question link where i found the First query i have mentioned.

WhereUSed=Null Report

on October 20, 2020.
Cancel
Add Comment
Cancel
2 Answer(s)
Try : for Orphan:
select distinct item_number from item where id not in (select i.id from bom b, item i where b.item_number=i.item_number) and delete_flag is null and class=10000;
Cancel
Agile Angel Answered on October 21, 2020.
Add Comment
Cancel

Priya,

You might also try this… It seems to run fairly quickly.

SELECT * FROM
(SELECT I.ITEM_NUMBER, COUNT(B.COMPONENT) CNT FROM AGILE.ITEM I
LEFT JOIN AGILE.BOM B ON I.ID = B.COMPONENT
WHERE I.CLASS = 10000
GROUP BY I.ITEM_NUMBER)
WHERE CNT = 0;

It is a bit over simplified though. This would return only items that have NEVER been on a BOM. As you are probably already aware, it is possibly for an item to be a component in the BOM table, but not be on the latest BOM because the item had been redline removed on a change order.  This query does account for that use case, but with a little more work, it could.

Steve

Cancel
Agile Professional Answered on October 26, 2020.
Add Comment
Cancel

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.