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.

Agile User Asked on April 14, 2021 in Agile PLM (v9).
Add Comment
1 Answer(s)

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’;

Agile Talent Answered on May 3, 2021.

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.

on May 29, 2021.
Add Comment

Your Answer

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