Skip to main content
L
lnunes10-Marble
10-Marble
October 14, 2021
Question

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

  • October 14, 2021
  • 3 replies
  • 2283 views

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));

 

3 replies

M
MikeLockwood22-Sapphire I
22-Sapphire I
October 14, 2021

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

avillanueva
avillanueva23-Emerald I
23-Emerald I
October 18, 2021

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

M
MikeLockwood22-Sapphire I
22-Sapphire I
October 18, 2021

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.

Terms & ConditionsAccessibility statement