Community Tip - You can Bookmark boards, posts or articles that you'd like to access again easily! X
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
Solved! Go to Solution.
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
This is not really part of Windchill API per say but core Java. Its the indexOf() method.
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)
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
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
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.