Skip to main content
A
adev1-Visitor
1-Visitor
January 29, 2016
Solved

How to get the Latest Version Iteration

  • January 29, 2016
  • 3 replies
  • 7269 views

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;

Best answer by BineshKumar1

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

3 replies

B
BineshKumar11-VisitorAnswer
1-Visitor
January 29, 2016

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

R
RandyJones20-Turquoise
20-Turquoise
January 29, 2016
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 ; 

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.

M
22-Sapphire I
MikeLockwood22-Sapphire I
22-Sapphire I
January 29, 2016

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.

    M
    MainuddinShaik6-Contributor
    6-Contributor
    August 1, 2024

    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)

 ;

     

    Terms & ConditionsAccessibility statement