How to realize the SQL by Windchill API?

[WhiteChen]

Hi,

Need help to create QuerySpec by using Windchill API, the sql like below ：

select LENGTHSCALE from EPMDOCUMENT WHERE IDA3MASTERREFERENCE IN (SELECT ida2a2 from EPMDOCUMENTMASTER WHERE DOCUMENTNUMBER='TEST.PRT')

Who can help me?

[jmomber]

This is the query you want, but for WTParts. Just change to EPMDocument and EPMDocumentMaster. It also doesn't restrict to just the LENGTHSCALE value, but it's most of what you asked for. There are probably examples in the Customization Guide for just selecting a specific column.

QuerySpec spec = new QuerySpec();

int wtpartIndex = spec.addClassList(WTPart.class, true);

int wtpartMasterIndex = spec.addClassList(WTPartMaster.class, false);

ClassAttribute ida3MasterReference = new ClassAttribute(WTPart.class, WTPart.MASTER_REFERENCE + "." + WTAttributeNameIfc.REF_OBJECT_ID);

ClassAttribute masterIda2a2 = new ClassAttribute(WTPartMaster.class, WTAttributeNameIfc.ID_NAME);

SearchCondition masterNumber = new SearchCondition(WTPartMaster.class, WTPartMaster.NUMBER, SearchCondition.EQUAL, "TEST.PRT");

SearchCondition masterJoin = new SearchCondition(ida3MasterReference, SearchCondition.EQUAL, masterIda2a2);

spec.appendWhere(masterJoin, new int[]{wtpartIndex, wtpartMasterIndex});

spec.appendAnd();

spec.appendWhere(masterNumber, new int[]{wtpartMasterIndex});

System.out.println(spec.toString());

QueryResult qR = PersistenceHelper.manager.find((StatementSpec)spec);