How to get the Latest Version Iteration

[adev]

Hello Team,

Is this any way to get the latest version iteration data list for the Windchiill object I am using this query and it gives me latest for all the Version I am looking for only latest version values

see the screen shot

for example if I have part of version A,B,C then I want only C version data

select WTPartMaster.WTPARTNUMBER "Number" , WTPartMaster.name as "Name", wtpart.VERSIONIDA2VERSIONINFO ||'.'|| wtpart.ITERATIONIDA2ITERATIONINFO as "Version"  from wtpart, WTPartMaster

where wtpart.IDA3MASTERREFERENCE = WTPartMaster.ida2a2 and WTPartMaster.wtpartnumber = 'W-00002' and wtpart.LATESTITERATIONINFO = 1;

[BineshKumar1 · Accepted solution]

Use max of branchiteratiooninfo

select WTPartMaster.WTPARTNUMBER as PartNumber , WTPartMaster.name as "Name", wtpart.VERSIONIDA2VERSIONINFO ||'.'|| wtpart.ITERATIONIDA2ITERATIONINFO as "Version"  from wtpart, WTPartMaster,
(SELECT MAX(wp.branchIditerationInfo) AS max, wp.idA3masterReference
   FROM wtpart wp GROUP BY wp.idA3masterReference
   ) branchmax
where wtpart.IDA3MASTERREFERENCE = WTPartMaster.ida2a2  and wtpart.LATESTITERATIONINFO = 1 and wtpart.branchiditerationinfo in (branchmax.max)  order by PartNumber ;

Thank you,

Binesh Kumar

Medtronic - MITG

[RandyJones]

Binesh Kumar wrote:

Use max of branchiteratiooninfo

1. select WTPartMaster.WTPARTNUMBER as PartNumber , WTPartMaster.name as "Name", wtpart.VERSIONIDA2VERSIONINFO ||'.'|| wtpart.ITERATIONIDA2ITERATIONINFO as "Version"  from wtpart, WTPartMaster, 
2. (SELECT MAX(wp.branchIditerationInfo) AS max, wp.idA3masterReference 
3.    FROM wtpart wp GROUP BY wp.idA3masterReference 
4.    ) branchmax 
5. where wtpart.IDA3MASTERREFERENCE = WTPartMaster.ida2a2  and wtpart.LATESTITERATIONINFO = 1 and wtpart.branchiditerationinfo in (branchmax.max)  order by PartNumber ; 

select WTPartMaster.WTPARTNUMBER as PartNumber , WTPartMaster.name as "Name", wtpart.VERSIONIDA2VERSIONINFO ||'.'|| wtpart.ITERATIONIDA2ITERATIONINFO as "Version"  from wtpart, WTPartMaster,
(SELECT MAX(wp.branchIditerationInfo) AS max, wp.idA3masterReference
   FROM wtpart wp GROUP BY wp.idA3masterReference
   ) branchmax
where wtpart.IDA3MASTERREFERENCE = WTPartMaster.ida2a2  and wtpart.LATESTITERATIONINFO = 1 and wtpart.branchiditerationinfo in (branchmax.max)  order by PartNumber ;

How do we know that branchiditerationinfo is guaranteed to be "in order"? It will probably always be in order but I wouldn't think it would be guaranteed to be. After all branchiditerationinfo is only the unique id of the controlbranch table. I would think to be guaranteed, you have the latest Version, you would want to sort by wtpart.versionsortida2versioninfo.

[MikeLockwood]

In query builder Latest Iteration is a direct selection, but Latest Revision requires a sub-select that finds the max of VERSIONSORTID (there are multiple CS articles on this).  May be helpful to examine the resulting SQL from such a query builder report.