cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

QuerySpec for DB query

SOLVED
Highlighted
Participant

QuerySpec for DB query

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';

1 ACCEPTED SOLUTION

Accepted Solutions

Re: QuerySpec for DB query

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());

2 REPLIES 2

Re: QuerySpec for DB query

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());

Re: QuerySpec for DB query

Thank you Jamie,

this is very helpful

Announcements

Thingworx Navigate content has a new home! Click here to access the new Thingworx Navigate forum! ______________________________ Check out the Windchill Tips Board! We're talking about Whirlpool's use of digital twin, augmented reality, and data-driven design!

The NAVIGATE WORKING GROUP is here! Come innovate with PTC!

Sign up for a Working Group