topic Re: Windchill query with INSTR in Windchill Customization

Windchill query with INSTR

IvanFeofilov — Fri, 15 Mar 2024 13:15:03 GMT

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

Re: Windchill query with INSTR

avillanueva — Fri, 16 Dec 2022 13:23:05 GMT

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

Re: Windchill query with INSTR

HelesicPetr — Mon, 19 Dec 2022 07:35:58 GMT

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

Re: Windchill query with INSTR

IvanFeofilov — Mon, 19 Dec 2022 07:48:36 GMT

Yes,

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

Re: Windchill query with INSTR

HelesicPetr — Mon, 19 Dec 2022 09:01:58 GMT

@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

Re: Windchill query with INSTR

joe_morton — Mon, 19 Dec 2022 19:02:08 GMT

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

Re: Windchill query with INSTR

IvanFeofilov — Tue, 20 Dec 2022 04:41:46 GMT

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

Re: Windchill query with INSTR

IvanFeofilov — Tue, 20 Dec 2022 13:24:44 GMT

Are you kidding?

I mean using Windchill Java QueryStatement

Re: Windchill query with INSTR

avillanueva — Tue, 20 Dec 2022 14:38:35 GMT

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

Re: Windchill query with INSTR

IvanFeofilov — Wed, 21 Dec 2022 04:48:11 GMT

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

Re: Windchill query with INSTR

HelesicPetr — Wed, 21 Dec 2022 07:24:14 GMT

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