cancel
Showing results for 
Search instead for 
Did you mean: 
Security Alert Log4j Security Vulnerability. Click here to know more.
cancel
Showing results for 
Search instead for 
Did you mean: 

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

lnunes
5-Regular Member

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

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 3
mlockwood
20-Turquoise
(To:lnunes)

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

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

mlockwood
20-Turquoise
(To:lnunes)

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.

Announcements