cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

How to get the Latest Version Iteration

adev
1-Newbie

How to get the Latest Version Iteration

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;

1 ACCEPTED SOLUTION

Accepted Solutions
BineshKumar1
12-Amethyst
(To:adev)

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

View solution in original post

3 REPLIES 3
BineshKumar1
12-Amethyst
(To:adev)

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

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.maxorder 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
20-Turquoise
(To:adev)

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.

Announcements