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
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.