Query Builder returning blank report if attribute string value is empty

[cswaner]

Greetings Gurus,
I am trying to query the part name, number, and a string value of IBA from WTPart. When the IBA has an empty value, the report returns no results at all. I would like to at least have it return the other attributes and show the IBA is empty.

Here is what I have:

[MikeLockwood]

May need to use Outer Join on the From tab for String Value relative to
Part.

Returns Parts which do not yet have a value for String Value.

[cswaner]

Greetings Mike,
Thank you for the feedback. As you can see from my previous post, I did try the outer join precisely as you suggested. From some reason it does not work on my system. Can you tell me which version of Windchill you used for the images you posted?

I am using Windchill 10.1 m010 on MS SQL (not sure of the version).

I suspect this is either a bug with WC 10.1 m010 or the SQL does not work properly with MS SQL.

I have an open case with PTC to determine.

Thanks,
Cam

Cameron S Swaner
Windchill PLM Specialist/Architect
Engineering Systems Group
Flowserve Corporation

[MikeLockwood]

Using 10.2 M020 - with Oracle 11g R2.



SQL - may be helpful to examine but probably not





SELECT

A0.classnameA2A2,A0.idA2A2,A1.value C1C1

FROM

WTPart A0,

StringValue A1,

(SELECT A2.idA2A2

FROM BooleanDefinition A2

UNION ALL

SELECT A2.idA2A2

FROM FloatDefinition A2

UNION ALL

SELECT A2.idA2A2

FROM IntegerDefinition A2

UNION ALL

SELECT A2.idA2A2

FROM ReferenceDefinition A2

UNION ALL

SELECT A2.idA2A2

FROM StringDefinition A2

UNION ALL

SELECT A2.idA2A2

FROM TimestampDefinition A2

UNION ALL

SELECT A2.idA2A2

FROM URLDefinition A2

UNION ALL

SELECT A2.idA2A2

FROM UnitDefinition A2) A2

WHERE

((A1.idA3A4(+) = A0.idA2A2) AND

(A1.idA3A6 = A2.idA2A2))

[cswaner]

I think I see the error in my ways. The part type that has the attribute associated to it is a WTPart soft type, further, the attribute I was searching for is a classification attribute.

So, it works as expected with attributes associated to the soft type. Now I need to figure out how to join the attribute value to the soft type through the classification node?

Any ideas? Has anyone built a successfull query for classification nodes and attribute value?

Thanks,

Cam

PLM Solution Architect

[LewisLawrence]

The attached zip file should get you what you need. It is easiest to run two queries, one to get you the node info you need, then another to return the wtparts by an internal node attribute. This works best using a wildcard against classification hierarchy ID, which lets you go up the structure and get everything from under a given node. Note that in Windchill 10.2 the classification object model is different and these queries won’t work.

In Reply to Cameron Swaner:

I think I see the error in my ways. The part type that has the attribute associated to it is a WTPart soft type, further, the attribute I was searching for is a classification attribute.

So, it works as expected with attributes associated to the soft type. Now I need to figure out how to join the attribute value to the soft type through the classification node?

Any ideas? Has anyone built a successfull query for classification nodes and attribute value?

Thanks,

Cam

PLM Solution Architect

-----

Lewis

[cswaner]

Thanks: This is another piece to the puzzle.

What I am trying to get it the Part information including attribute values from specific attributes in the classification node.

How can I get the attribute values from an string attribute that is part of a classification node?

I know I can get them with attribute definition, string value, etc. However, I need to make the proper outerjoin to allow for a null value in one of the classification attributes, otherwise my entire report is blank.

PLM Solution Architect