SQL to extract the items based on conditions
Hi, I’m trying to extract the items for the below criterias through SQL. Should I use any inbuilt identifiers or what would be the best way to write this SQL?
1. Extract items which are approved (for these items, there will not be any pending changes)
2. Extract items which has previous revision approved and current revision is WIP. (pending changes could be 1 or more than 1)
3. Extract items which has only one revision and it is WIP ( shouldn’t be approved items)
Thanks.
Dear Agile PLM User,
Here’s a query you may be able to use directly, or may be able to modify for your purposes. Try running this query against a test part you just created, then again after placing the item onto an ECO. Then release that ECO, and then place the item once more onto a new ECO that is not yet released. Check out the differences in the records. I hope this is helpful.
SELECT I.ID ITEM_ID, I.Item_Number,
r.old_revnumber,
R.rev_number ,
NVL(N1.Description, ‘Preliminary’) Lifecycle ,
NVL(C.Change_Number,’N/A’) Change_Order ,
NVL(N2.Description,’No ECO’) Co_Status,
C.ID CHANGE_ID,
C.RELEASE_DATE,
R.Latest_Flag
FROM Agile.Item I
LEFT JOIN Agile.Rev R ON R.Item = I.Id AND r.site = 0
LEFT JOIN Agile.Nodetable N1 ON R.Release_Type = N1.Id
LEFT JOIN AGILE.CHANGE C ON R.CHANGE = C.ID
LEFT JOIN AGILE.NODETABLE N2 ON C.STATUS = N2.ID
WHERE i.item_number = ‘TEST-PART-NUMBER’;
Dear Stevend17,
Thanks for your response. This query is really helpful. However, I’m unable to get the items that do not have any pending changes.
What would be the right combination of filters to apply in this query to extract only the items that are not undergoing any changes? But these items may have previous changes in the history. Thanks.