In our environment, an Item revision can have multiple Release status. Suppose, if there are two release status 'R1' and 'R2'. We need to find Item revisions with status 'R1' but not with status 'R2'. Basically, query should fetch all the Item revision with either status 'R1' only or Status 'R1' and any status other than 'R2'.
This query can be implemented through customization. But, let me know, if there any option available without customization.
Solved! Go to Solution.
I've had only limited success with these types of queries but I wonder, have you tried a Item Revision class query with two clauses (I appologize writing the clauses in pseudo-code):
status = R1
AND status != R2
When applying a status, (EPM-set-status) do you ADD or do you REPLACE the status ?
And are you querying the revision_status_list or the last_status ?
You can also use a list of valid status: e.g. "R1,R3,R4" - you have to enter this list by typing within the query builder, you can not do a multi-select ( at least in TC10.1 ).
Production: NX126.96.36.199 MP04 + TC10.1.7.1
Testing: NX188.8.131.52 MP07 + TC184.108.40.206
Thank you for the reply!
We want Item revisions with Release status R1 but not R2. Is there any approach to prepare such a query from query builder?
Hi @Nihon. If you are looking for an interactive query then you won't be able to do this with query builder.
If you set up a query like the following;
(I used out of the box statuses, but the idea is the same.)
You end up with an SQL query on your database that looks a bit like this;
SELECT DISTINCT t_02.puid FROM PRELEASE_STATUS_LIST t_01 , PITEMREVISION t_02 , PRELEASESTATUS t_03 WHERE t_01.pvalu_0 = t_03.puid AND t_01.puid = t_02.puid AND UPPER(t_03.pname) = UPPER( 'TCM Released' ) AND UPPER(t_03.pname) != UPPER( 'Approved' )
If we have 2 objects, one with only TCM Released, and one with both TCM Released and Approved and you conceptually do the joins then you get a virtual table that looks like this;
Row UID Status
1 Obj1 TCM Released
2 Obj2 TCM Released
3 Obj2 Approved
Then the criteria run against each row independently.
Row 1 qualifies because status equals TCM Released and it's also not equal to Approved.
Row 2 qualifies for the same reason.
Row 2 doesn't qualify.
So you always get Obj1 and Obj2 out.
To be able to perform such a query through query builder would require it to be able to build queries with constructs such as 'NOT EXISTS( subquery )' or query1 INTERSECTS query2. This is currently not possible.
If you are planning to try and use this as a filter for Workflow targets then there are techniques that will let you get to the set of targets matching these conditions.