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.

[MS_10622294]

The below query gives the latest object information 

 
select  distinct   
t1.wtdocumentnumber,
 t2.versionida2versioninfo +'.'+ 
 t2.iterationida2iterationinfo iteration_id,
 t2.versionSortIdA2versionInfo,
  t2.branchiditerationinfo 
from 
	 wtdocument t2,
	 wtdocumentmaster t1, 
	 
where 
	t2.ida3masterreference=t1.ida2a2  and 
	l1.ida2a2=t2.ida3containerreference  and 
	t2.IDA2TYPEDEFINITIONREFERENCE=t5.ida2a2   and
	t1.wtdocumentnumber='number'
and t2.latestiterationinfo = 1 AND  
	t2.ida2A2 NOT IN ( select CB1.IDA3C5 
					from    controlbranch CB1  
		            where cb1.classnamekeyb5 = 'wt.doc.WTDocumentMaster' 
						  AND t1.ida2a2 = cb1.ida3b5)

 ;