How extract Display enumerated value of an object?

[AndreaP]

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

[hlafkir]

Hi,

Please take a look into the LWCLocalizablePropertyValue table. here are the display values (with column for local)

good luck

[AndreaP]

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?

[hlafkir]

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

[AndreaP]

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

[hlafkir]

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

[AndreaP]

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 

[hlafkir]

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

[AndreaP]

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 

[awei3]

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>

[ptc-617114]

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