Skip to main content
G
gmydoor-210-Marble
10-Marble
July 22, 2020
Solved

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

  • July 22, 2020
  • 1 reply
  • 3401 views

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.

Best answer by hlafkir

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

1 reply

H
hlafkir5-Regular MemberAnswer
5-Regular Member
July 22, 2020

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

    G
    gmydoor-210-MarbleAuthor
    10-Marble
    July 22, 2020

    Thanks a lot . I will check and revert.

    Terms & ConditionsAccessibility statement