Query spec for multiple objects

[TG_10272716]

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]

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

[TG_10272716]

Hi @HelesicPetr,

thank you for your reply, I would greatly appreciate your help.

Best regards

[HelesicPetr · Accepted solution]

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

[TG_10272716]

Hello @HelesicPetr, thanks for the example.

Just for my understanding; if I use SearchCondition.NO_OUTER_JOIN instead of SearchCondition.LEFT_OUTER_JOIN does this equal a regular (inner) join or a full join?

Best regards

[HelesicPetr]

Hi @TG_10272716 

Good question, 

Try it. I've never thought about it.

PetrH