cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Learn all about the Community Ranking System, a fun gamification element of the PTC Community. X

Windchill query with INSTR

IvanFeofilov
7-Bedrock

Windchill query with INSTR

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

ACCEPTED SOLUTION

Accepted Solutions

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.  

 

HelesicPetr_0-1671606087260.png

 

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

View solution in original post

10 REPLIES 10

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

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

Yes,

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

@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

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.  

 

HelesicPetr_0-1671606087260.png

 

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

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

 

 

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

Are you kidding?

I mean using Windchill Java QueryStatement

@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?

@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.

Announcements

Top Tags