How to use a own created virtual table in the query builder (report template) ?

[MrPresident]

Hello Community !

I created a virtual table on the windchill database to use them in the query builder of an report template, but unfortunately the virtual table is not visible in the object list of the tab "Tables and Joins" of the report template (edit mode). How to make the virtual table visible in this list ?

Thanks for any hints !

[olivierfresse]

Hi,

I'm afraid you can't : The query builder uses Windchill's persistence layer to build the tables list.

Only tables associated to persistent objects are displayed.

However, depending of what you want to do, you may use the fact that the query builder can be customized.

You can create custom macros : https://www.ptc.com/en/support/article/CS260538?&language=en&posno=1&q=query%20builder%20java&source=search

You can call java methods in a report : https://www.ptc.com/en/support/article/CS114482?&language=en&posno=5&q=query%20builder%20java&source=search

Here is an example of code to use SQL with Windchill's connection (in groovy)

import wt.method.MethodContext
import wt.pom.WTConnection
import wt.queue.ProcessingQueue

import java.sql.ResultSet

MethodContext methodcontext = MethodContext.getContext();
wtconnection = (WTConnection) methodcontext.getConnection();

//String statement = "SELECT WTPartNumber FROM WTPartMaster where WTPartNumber=?";
String statement = "select doc.LATESTITERATIONINFO from EPMDOCUMENT doc where doc.IDA3MASTERREFERENCE=?";

theStatement = wtconnection.prepareStatement(statement);

// master ida2a2 ...
theStatement.setString(1, "0000001306");

ResultSet rs = theStatement.executeQuery();
if(rs.next()){

  println rs.getString(1)
}

Maybe you can use this to returns values of you custom table. 

Hope it helps,

[MrPresident]

Hi olivierfresse !

Even I would prefer the solution with a virtual table, your Java method suggestion also works, but the effort is definitely higher. 😞

Anyway, thanks for the tip !

Have a nice weekend !