Query spec for multiple objects

Question

Hi,

I wrote a SELECT statement that basically queries all Change Activities for all Change Orders (please ignore the * selector for now):

SELECT
*
FROM WTCHANGEORDER2 
INNER JOIN INCLUDEDIN2 ON WTCHANGEORDER2.branchiditerationinfo = INCLUDEDIN2.BRANCHIDA3A5 
INNER JOIN WTCHANGEACTIVITY2 ON INCLUDEDIN2.BRANCHIDA3B5 = WTChangeActivity2.branchiditerationinfo

Now I would like to convert this to a Windchill QuerySpec.

What I am trying to do is to get attributes for both the Change Activity and the Change Order. For now I used the * selector but I would like to select attributes from WTChangeActivity2 and WtChangeOrder2.

As of my intuition, you need to specify an "output" class for your query spec, so what class or type those objects from the Query spec belong to but in my case this would be mixed because I need attributes from both types.

Is there a way how I can do this or do I need to split my query up (i.e. first query all Change Orders and then query all Change Activities per Change Order)

Thanks in advance and best regards

HelesicPetr · Accepted Answer

Hello @TG_10272716

Here is example, how to search object based on EPMDocument class and IBA value

Class searchClass = EPMDocument.class;
String searchIBA = "IDNameIBA";
String seachVALUE = "value";
QuerySpec queryspec;
try
{
queryspec = new QuerySpec();
queryspec.setAdvancedQueryEnabled(true);
int indexObjectEPM = queryspec.appendClassList(searchClass, true);
int indexObjectSTRV = queryspec.appendClassList(StringValue.class, true);
int indexObjectIBA = queryspec.appendClassList(StringDefinition.class, true);
SearchCondition condition17 = new SearchCondition(
				searchClass, WTAttributeNameIfc.ID_NAME,
				StringValue.class, "theIBAHolderReference.key.id");
condition17.setOuterJoin(SearchCondition.LEFT_OUTER_JOIN);
queryspec.appendWhere(condition17,
											new int[]{indexObjectEPM,
															indexObjectSTRV});
queryspec.appendAnd();
condition17 = new SearchCondition(
				StringValue.class, "definitionReference.key.id",
				StringDefinition.class, WTAttributeNameIfc.ID_NAME);
condition17.setOuterJoin(SearchCondition.LEFT_OUTER_JOIN);
queryspec.appendWhere(condition17,
											new int[]{indexObjectSTRV,
															indexObjectIBA});
queryspec.appendAnd();
CompositeWhereExpression andCondition = new CompositeWhereExpression(LogicalOperator.AND);
andCondition.append(new SearchCondition(StringDefinition.class,
																				StringDefinition.NAME,
																				SearchCondition.LIKE,
																				searchIBA), new int[]{indexObjectIBA});
queryspec.appendWhere(andCondition, new int[]{indexObjectIBA, indexObjectIBA});

queryspec.appendAnd();
andCondition = new CompositeWhereExpression(LogicalOperator.AND);
andCondition.append(new SearchCondition(StringValue.class,
																				StringValue.VALUE,
																				SearchCondition.LIKE,
																				seachVALUE), new int[]{indexObjectIBA});
queryspec.appendWhere(andCondition, new int[]{indexObjectSTRV, indexObjectSTRV});
queryspec.appendAnd();
andCondition = new CompositeWhereExpression(LogicalOperator.AND);
andCondition.append(new SearchCondition(searchClass,
																				"iterationInfo.latest",
																				"TRUE"), new int[]{indexObjectEPM});
queryspec.appendWhere(andCondition, new int[]{indexObjectEPM});
queryspec.appendOrderBy(new OrderBy(new ClassAttribute(searchClass, "master>number"), false), new int[]{0});
queryspec.appendOrderBy(new OrderBy(new ClassAttribute(searchClass, "versionInfo.identifier.versionSortId"), false), new int[]{0});

QueryResult queryRes = PersistenceServerHelper.manager.query(queryspec);

PetrH

HelesicPetr · Answer

Hi @TG_10272716 Yes there is a way..On Monday I will send an example how to use inner join in the QuerySpec if no one is faster. .PetrH

Sign up

Please use your PTC eSupport account.

Welcome to the PTC Community

Please use your PTC eSupport account.

Scanning file for viruses.

This file cannot be downloaded