SQL - How to get wtpartmaster.ida2a2 and wtpart.idA2A2 from a WTPart.Partnumber?

[lnunes]

How to select the wtpartmaster.ida2a2 and wtpart.idA2A2 from a given WTP Partnumber?

I would like to provide only the WTPart Partnumber to enable the usage of the following query (using the latest version-iteration of the provided WTPart). This query is going to provide the EPMDoc association to a WTPart:

SELECT
epmdocument.branchiditerationinfo,
epmbuildrule.branchida3a5,
epmbuildrule.buildtype,
wtpartmaster.wtpartnumber,
epmdocumentmaster.documentnumber
FROM epmbuildrule, epmdocument, epmdocumentmaster, wtpart, wtpartmaster, epmBuildHistory
WHERE
((epmbuildrule.branchida3a5 = epmdocument.branchiditerationinfo)
AND (epmdocument.ida3masterreference = epmdocumentmaster.ida2a2)
AND (wtpart.branchiditerationinfo = epmbuildrule.branchida3b5)
AND (wtpart.ida3masterreference = wtpartmaster.ida2a2)
AND (epmBuildHistory.idA3B5 = wtpart.idA2A2)
AND (epmBuildHistory.idA3A5 = epmdocument.idA2A2));

[MikeLockwood]

In general, do this via a query builder report rather than direct SQL.

[avillanueva]

Hope this example helps. There is a table in the FROM clause that gets latest versions. I filtered on certain WTPARTNUMBER which you can change to what you want.

SELECT VERS.WTPARTNUMBER, M2.NAME, P2.VERSIONIDA2VERSIONINFO, P2.ITERATIONIDA2ITERATIONINFO, M2.DEFAULTUNIT, M2.DEFAULTTRACECODE, P2.SOURCE
FROM WTPART P2, WTPARTMASTER M2,
(SELECT M.WTPARTNUMBER, MAX(P.VERSIONSORTIDA2VERSIONINFO) SORTID
FROM WTPART P, WTPARTMASTER M
WHERE
P.IDA3MASTERREFERENCE = M.IDA2A2 AND
P.LATESTITERATIONINFO = 1 AND
P.IDA3VIEW = 1445 AND
M.WTPARTNUMBER NOT LIKE 'PRT%'
GROUP BY M.WTPARTNUMBER) VERS
WHERE
M2.WTPARTNUMBER = VERS.WTPARTNUMBER AND
P2.VERSIONSORTIDA2VERSIONINFO = VERS.SORTID AND
P2.LATESTITERATIONINFO = 1 AND
P2.IDA3MASTERREFERENCE = M2.IDA2A2

[MikeLockwood]

Just an explanatory comment: The reason it's not straight forward to get the latest Rev (like latest iteration) is that in any Windchill system, one can define any character sequence as a Revision sequence, allowing skips (e.g. 1, 2, 3.... -, A, B...).

This requires relatively complex subselect every time.