Windchill query with INSTR

[IvanFeofilov]

Hello guys,

Could you please share an example of using Windchill Java api with function INSTR .

SQL e.g.
SELECT count(*)
FROM LCSPalette, LCSProduct
WHERE INSTR(LCSPalette.PTC_STR_12TYPEINFOLCSPALETTE, LCSProduct.PTC_STR_21TYPEINFOLCSPRODUCT) > 1

[avillanueva]

This is not really part of Windchill API per say but core Java. Its the indexOf() method.

https://docs.oracle.com/en/java/javase/11/docs/api/java.base/java/lang/String.html#indexOf(java.lang.String)

ahh, but I see you are looking for building a QuerySpec? Then you want wt.query.SQLFunction class. It has all the functions listed there.

In your installation, you can view the JavaDoc API here:

<hostname>/Windchill/wt/clients/library/api/wt/query/SQLFunction.html

[HelesicPetr]

Hi @avillanueva 

He maybe means the wt.query.QuerySpec

I've never found documentation for it. @IvanFeofilov needs to experiment with the definition of query conditions.

PetrH

[IvanFeofilov]

Yes,

Interesting in example of wt.query.SQLFunction with MINUS or INTERSECT Compound Statements (wt.query.CompoundQuerySpec)

[HelesicPetr]

@IvanFeofilov 

Nice example, but it is not official documentation :D. 

This way you can go through all "query" classes and if you are lucky you can find better examples. 

PetrH

[HelesicPetr · Accepted solution]

Hi @IvanFeofilov 

The Christmas is coming 😄

The sql function what you need is it an IN_STRING? it has just different internal name in the SQLFunction API but it could be what you need.  

Here I share an example, how the function can be used in querySpec, but with different function types.

QuerySpec querySpec = new QuerySpec();
			querySpec.setAdvancedQueryEnabled(true);

			int processQueueINT = querySpec.appendClassList(ProcessingQueue.class, true);
			int queryEntryINT = querySpec.appendClassList(QueueEntry.class, true);
			ClassAttribute classAttribute = new ClassAttribute(ProcessingQueue.class, "name");
			SQLFunction sqlFunction1 = SQLFunction.newSQLFunction("LENGTH", new ColumnExpression[]{classAttribute});
			SQLFunction sqlFunction2 = SQLFunction.newSQLFunction("SUB_STRING", new ColumnExpression[]{classAttribute, sqlFunction1, new ConstantExpression(1)});
			SearchCondition not_in = new SearchCondition(sqlFunction2, "NOT IN", new ArrayExpression(new String[]{"H", "L", "M"}));
			SearchCondition like = new SearchCondition(ProcessingQueue.class, "name", "LIKE", "PublisherQueue%");
			SearchCondition procesingID = new SearchCondition(ProcessingQueue.class, "thePersistInfo.theObjectIdentifier.id", QueueEntry.class, "queueRef.key.id");

			querySpec.appendWhere(not_in, processQueueINT);
			querySpec.appendAnd();
			querySpec.appendWhere(like, processQueueINT);
			querySpec.appendAnd();
			querySpec.appendWhere(procesingID, processQueueINT, queryEntryINT);

QueryResult jobQS = PersistenceServerHelper.manager.query(querySpec);

Hope this can help.

PetrH

[joe_morton]

Are you trying to use the "In String" function in a query builder report? Database Functions for query builder are documented here: http://support.ptc.com/help//wnc/r12.1.0.0/en/index.html#page/Windchill_Help_Center/querybuilder/QBSelectOrConstrainDatabaseFunction.html

[IvanFeofilov]

Yes, but how I can transform QML into Windchill query API?

[IvanFeofilov]

Are you kidding?

I mean using Windchill Java QueryStatement

[avillanueva]

@IvanFeofilov, ignore that. There is confusion in your request. We know you doing a regular old QuerySpec in Java. I think we've given you what you need to start experimenting on your own right?

[IvanFeofilov]

@avillanuevaI always could experement for now have no time. So I asked about an example of QuerySpec  with two arguments function, first argument from result select.