The community will undergo maintenance on October 16th at 10:00 PM PDT and will be unavailable for up to one hour.
Hello everybody, I'm trying to retrive some global enumerated value of WTDocument from the Windchill 11.0 M030 database with a SQL query.
I used the queries of https://www.ptc.com/en/support/article/CS174022
but what i can get are just the Internal values of the attributes, not the Display values.
(column NAME of [LWCEnumerationEntry])
Which table contains the DISPLAY Value of the enumerated attributes?
How these attributes values are linked to the objects (eg WTDocument) ?
I can see the stringValue linked to the object, but again it contains the internal value and I need instead the display value.
Can you point me to a datamodel documentation that covers this topic?
Thank you
Hi,
Please take a look into the LWCLocalizablePropertyValue table. here are the display values (with column for local)
good luck
Thank you! now I'm able to see the display value in the database, LWCLocalizablePropertyValue.value
and link it to the others tables
LWCLocalizablePropertyValue.IDA3B4-->LWCEnumEntry.IDA2A2
LWCEnumEntryLink.IDA3B4-->LWCEnumEntry.IDA2A2
LWCLocalizablePropertyValue.IDA3C4-->LWCMasterEnumerationDefinition.IDA2A2
I still miss how these values are linked to an object;
for example on WTDocument normal strings vaues are linked :
WTDocument <--(IDA3A4) stringValue (IDA3A6) --> stringDefinition
How is the join in this case?
Hi,
This article should help you to link the required tables :
https://www.ptc.com/en/support/article?n=CS302379
Hope this will help.
Another hint :
You can log SQL statements to see the query executed from Windchill to Database by using advices in this article :
https://www.ptc.com/en/support/article/CS46545
or, maybe better , add wt.pom.sql using the steps in Windchill GUI :
https://www.ptc.com/en/support/article/CS145146
This will show you how Windchill is building queries for any operations. Lot of lines 🙂
Hicham
Thank you for interesting readings! I made some tests with this info but I still not completed my query.
Unfortunately this article query's is extracting the TYPE from the WTPart, and there is a dedicated column to link the tables WTTypeDefinition and WTPart
wttd.idA2A2 = d.idA2typeDefinitionReference
What I'm looking for now is: How a global enumerated CUSTOM value is linked to the Object (Let's say WTDocument) ?
I'm not able to connect a WTDocuemnt to it's enumerated value (LWCEnumEntry or LWCEnumEntryLink)
I guess I am missing some middle table that works like WTDocument <--stringValue--> stringDefinition
Thank you for helping
Hi Colleague,
Now, please check that one, and from the queryBuilder, preview the SQL generated to get a stringValue given a document instance :
https://www.ptc.com/en/support/article/CS247369
SELECT A0B.name,A4.value,A4.value2 FROM WTDocument A0,WTDocumentMaster A0B,StringDefinition A1,StringValue A2,LWCIBAAttDefinition A3,StringValue A4 WHERE (A0.idA3masterReference=A0B.idA2A2) AND (((A4.idA3A6(+) = ?) AND (A4.idA3A4(+) = A0.idA2A2))) AND ((A2.idA3A6 = A1.idA2A2) AND (A2.idA3A4 = A0.idA2A2) AND (A3.idA3A7 = A1.idA2A2))
[690066]
hicham
Hicham
All this issue comes directly from a disalignment between query builder result and database result:
With query builder Windchill is able to show the display values
but then when I use the same Query i got from querybuilder,
SELECT A1.value,A1.value2,A0.classnameA2A2,A0.idA2A2 FROM (SELECT A0.idA2A2,'wt.projmgmt.admin.Project2' classnameA2A2 FROM Project2 A0 WHERE (A0.branchIdA2typeDefinitionRefe = 505056)) A0 LEFT OUTER JOIN StringValue A1 ON ((A1.idA3A6 = 503093) AND (A1.idA3A4 = A0.idA2A2))
on the database, the result is the internal value (without spaces and in CAMEL Mode)
Now from your help, I understood that display name is stored in LWCLocalizablePropertyValue table, but I still miss the link between the tables..
in database LWCLocalizablePropertyValue is linked just to the LWCMasterEnumerationEntry and to LWCPropertyDefnition,(see attachment )
seems different from your DB
I think that here you will get what's you need :
select lpv.value, lpv.FR, dm.name
from wtdocument d,
wtdocumentmaster dm,
LWCIBAAttDefinition iad,
LWCEnumerationBasedConstraint ebc,
LWCMasterEnumerationDefinition med,
LWCEnumerationEntry ee,
LWCLocalizablePropertyValue lpv,
stringValue sv
where iad.name = 'MyEnum'
and ebc.idA3B4 = iad.idA2A2
and med.idA2A2 = ebc.idA3A5
and lpv.idA3C4 = med.idA2A2
and lpv.idA3B4 = ee.idA2A2
and d.ida3masterreference = dm.ida2a2
AND dm.name='myDocEnum'
and ee.name = sv.value2
and sv.idA3A4 = d.ida2a2;
Hoep this will help. Note that I could resolve that with the wt.pom.sql loggers.
Good luck
Hicham
Thank you all. I solved with this query; unfortunately the suggested link between the StringValue and the LWCEnumerationEntry is not unique (if the display value has changed in the database there are multiple lines), so I had to select the last modified LWCLocalizablePropertyValue in order to have a unique match.
SELECT
Val,
Display
FROM (
SELECT
ee.name AS Val,
lpv.value AS Display,
lpv.updateCountA2,
max(lpv.updateCountA2) over(partition by ee.name) AS maxUpd
FROM
LWCEnumerationEntry ee
INNER JOIN LWCLocalizablePropertyValue lpv ON ee.ida2a2 = lpv.ida3b4
INNER JOIN LWCEnumerationEntryLink eel ON eel.ida3b4 = ee.ida2a2
INNER JOIN LWCMasterEnumerationDefinition med ON eel.ida3a4 = med.ida2a2
WHERE med.name = 'BUSINESS_UNIT'
) T1
WHERE T1.updateCountA2=T1.maxUpd
Fiddled with this on my end. Seems to work at the moment.
LWCIBAATTDEFINITION appears to link the __DEFINITION table to a potential LWCENUMERATIONBASEDCONSTRAINT value
LWCENUMERATIONBASEDCONSTRAINT can then take you to a LWCMASTERENUMERATIONDEFINITION (or a LWCENUMERATIONDEFINITION, I haven't dug that deep though).
LWCMASTERENUMERATIONDEFINITION is what appears to ground the LWCLOCALIZABLEPROPERTY values, which are all possible values for the given incoming LWCMASTERENUMERATION
LWCENUMERATIONENTRY seems to hook off the STRINGVALUE2 value and can be used to downfilter the LWCLOCALIZABLEPROPERTY table to the enumeration in question.
I'm working a relevant project so this might change, but that seems to hold water at the moment on my end.
Also please note that you can use the HTML formatting option in the reply dialog to wrap your code in a <pre> and </pre> tag, makes it much easier to read.
SELECT WTPM.NAME, SD.NAME, SV.VALUE2, LPV.VALUE FROM WTPART WTP INNER JOIN WTPARTMASTER WTPM ON WTP.IDA3MASTERREFERENCE = WTPM.IDA2A2 INNER JOIN STRINGVALUE SV ON WTP.IDA2A2 = SV.IDA3A4 INNER JOIN STRINGDEFINITION SD ON SV.IDA3A6 = SD.IDA2A2 INNER JOIN LWCIBAATTDEFINITION IAD ON SD.IDA2A2 = IAD.IDA3A7 AND SD.CLASSNAMEA2A2 = IAD.CLASSNAMEKEYA7 INNER JOIN LWCENUMERATIONBASEDCONSTRAINT EBC ON IAD.IDA2A2 = EBC.IDA3B4 AND IAD.CLASSNAMEA2A2 = EBC.CLASSNAMEKEYB4 INNER JOIN LWCMASTERENUMERATIONDEFINITION MED ON EBC.IDA3A5 = MED.IDA2A2 AND EBC.CLASSNAMEKEYA5 = MED.CLASSNAMEA2A2 INNER JOIN LWCLOCALIZABLEPROPERTYVALUE LPV ON MED.IDA2A2 = LPV.IDA3C4 AND MED.CLASSNAMEA2A2 = LPV.CLASSNAMEKEYC4 INNER JOIN LWCENUMERATIONENTRY EE ON LPV.IDA3B4 = EE.IDA2A2 AND SV.VALUE2 = EE.NAME
WHERE WTP.IDA2A2 = <wtpart_oid>
bonjour
est il possible d'exécuter un ordre oracle SQL vers Windchill 11 PJL depuis l'extérieur avec un outil comme dbeaver ou Toad ?
si oui comment parametrer la connexion à la Db Windchill
Merci