SQL query to retrieve WTParts for given OR condition between 2 IBAs

[gmydoor-2]

HI all,

Can anyone please help me in optimizing the SQL query

. I have written a sql query to retrieve WTParts when 2 IBAs are given . (using OR, AND conditions).

SELECT A0.* FROM WTPart A0,UnitValue A1,StringValue A2,WTPARTMASTER A3 WHERE (A0.ida3masterreference=A3.ida2a2 and A3.WTPartNumber LIKE 'RT-3294%') AND (((A1.idA3A6 = 3853762) AND (A1.value = 0.037099) AND (A1.idA3A4 = A0.idA2A2)) OR ((A2.idA3A6 = 19907282) AND (A2.value = '22') AND (A2.idA3A4 = A0.idA2A2))) AND (A0.idA3view = 569) AND (A0.latestiterationInfo = 1) AND (A0.variation2 IS NULL ) AND (A0.statecheckoutInfo <> 'wrk').

But when this query is executed , its generating only 1 part more than 10k times . May I know if I have to modify the query.

Thanks.

[hlafkir · Accepted solution]

Hi,

Please add (add logger article) the logger wt.pom.sql  (logger article link)in a test environement and create a search in Windchill UI.

This logger will generate the right sql query into your method Server and help you to create the request sql syntax.

For an IBA not null in my environement I could get :

SELECT A0.*,A1.idA2A2,A1.updateCountA2,A1.idA3A6,A1.value,A1.value2
FROM WTPart A0,StringValue A1 WHERE (((A1.idA3A6 = 138794) AND (A1.idA3A4 = A0.idA2A2) AND ((A1.idA3A5 = 0) OR (A1.idA3A5 IS NULL )))) ;

and it is returning the expected results.

Hicham

[gmydoor-2]

Thanks a lot . I will check and revert.

[gmydoor-2]

Thanks hlafkir.

So the SQL query I managed to get is :

(SELECT A0.idA2A2 FROM WTPart A0,StringValue A1 WHERE (((A1.idA3A6 = 19907282) AND (A1.idA3A4 = A0.idA2A2)) AND (A1.value = '22')))UNION (SELECT A0.idA2A2 FROM WTPart A0,UnitValue A1 WHERE (((A1.idA3A6 = 3853762) AND (A1.idA3A4 = A0.idA2A2)) AND ((A1.value = 0.037099))))

May I also know what is the Windchill API for "UNION". I need to frame query spec using "UNION".

[hlafkir]

I think you are going on a tedious work while fetching part/IBA using IBA :

https://www.ptc.com/en/support/article/CS125877

Here a good alternatives in this article :

https://www.ptc.com/en/support/article/CS169049

or you can use also Query-Objects infoengine webject :

and use OR condition in Where parameter - note that the IBA are given with their internal reusable name

Anyway, if you need to know more about UNION in querySpec, here a good tuto :

https://support.ptc.com/help/windchill/wnc/r11.1_m020/whc_en/index.html#page/Windchill_Help_Center/WCCG_Serv_AdvQuery_SearchCondition.html

Good luck.

NB : why do you want to go through these technics ? a simple QueryBuilder report is not enough for you ?

Hicham

<%@page language="java" access="http|internal"%>
<%@taglib uri="http://www.ptc.com/infoengine/taglib/core" prefix="ie"%>

<ie:webject name="Get-Properties" type="MGT">
<ie:param name="ATTRIBUTE" data="wt.federation.ie.VMName"/>
<ie:param name="GROUP_OUT" data="properties"/>
</ie:webject>

<ie:webject name="Search-Objects" type="OBJ">
<ie:param name="INSTANCE" data="$(properties[0]wt.federation.ie.VMName[0])"/>
<ie:param name="type" data="wt.part.WTPart"/>

<!ie:param name="ATTRIBUTE_TYPE_CONTEXT" data="wt.fc.Persistable"/>
<ie:param name="where" data="(IBA|str ='MyString')|(IBA|DevelopmentCost='1')"/>
<ie:param name="attribute" data="name" delim=","/>
<ie:param name="group_out" data="objectsSearched"/>
</ie:webject>