QuerySpec for DB query

[adev-2]

Hi,

I need to create QuerySpec using given database query below is there any way I can covert below command to queryspec

select pm.name as PartName, pm.WTPARTNUMBER as PARTNUMBER, pr.NAMECONTAINERINFO as PRODUCTNAME from wtpart p, WTPARTMASTER pm, PDMLINKPRODUCT pr

where p.IDA3MASTERREFERENCE = pm.ida2a2 and p.IDA3CONTAINERREFERENCE = pr.ida2a2 and pr.NAMECONTAINERINFO = 'Test';

[jmomber · Accepted solution]

This does pretty much everything except setting the AS, and only returning 3 columns. You should be able to figure out the rest by checking out the QuerySpec section in the Windchill Customization guide (or whatever it is called these days).

The spec looks like this when you do .toString()

SELECT A0.*,A1.*,A2.* FROM wt.part.WTPart A0,wt.part.WTPartMaster A1,wt.pdmlink.PDMLinkProduct A2 WHERE (A0.idA3masterReference = A1.idA2A2) AND (A0.idA3containerReference = A2.idA2A2) AND (A2.namecontainerInfo = 'QA2')

         QuerySpec spec = new QuerySpec();

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

        int partMasterIndex = spec.addClassList(WTPartMaster.class, true);

        int productIndex = spec.addClassList(PDMLinkProduct.class, true);

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

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

        ClassAttribute productIda2a2 = new ClassAttribute(PDMLinkProduct.class, WTAttributeNameIfc.ID_NAME);

        ClassAttribute partIda3ContainerRef = new ClassAttribute(WTPart.class, WTPart.CONTAINER_REFERENCE + "." + WTAttributeNameIfc.REF_OBJECT_ID);

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

        SearchCondition containerJoin = new SearchCondition(partIda3ContainerRef, SearchCondition.EQUAL, productIda2a2);

        SearchCondition containerName = new SearchCondition(PDMLinkProduct.class, PDMLinkProduct.NAME, SearchCondition.EQUAL, "Test");

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

        spec.appendAnd();

        spec.appendWhere(containerJoin, new int[]{partIndex,productIndex});

        spec.appendAnd();

        spec.appendWhere(containerName, new int[]{productIndex});

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

[adev-2]

Thank you Jamie,

this is very helpful