Skip Headers

Agile Product Lifecycle Management SDK Developer Guide - Using APIs Release 9.3.3 E39307-01 |
| Contents

Previous | Next |

# 3 Creating and Loading Queries

An IQuery is an object that defines how to search for Agile PLM data. It defines a search similar to the searches that you can use in Agile Web Client. The search can have multiple search criteria (like an Advanced Search in Agile Web Client), or it can be a simple search that specifies only one criterion.

## 3.1 Creating a Query

To create and execute a query, you must first create an IQuery object. As with other Agile API objects, you create the object using the IAgileSession.createObject() method.

In its simplest form, the parameters that you pass with the createObject() method to create a query are the IQuery object type and the query class used in the search. In the following example, the query class is the Item class.

    try {
    IQuery query =
        (IQuery)session.createObject(IQuery.OBJECT_TYPE,
      ItemConstants.CLASS_ITEM_BASE_CLASS);
      query.setCaseSensitive(false);
      query.setCriteria("[Title Block.Number] starts with 'P'");
      ITable results = query.execute();
       } catch (APIException ex) {
        System.out.println(ex);
    }

The query class you specify with the createObject() method also includes objects from all of its subclasses. For example, if you search for objects in the Item class, the results include parts and documents. If you search for objects in the Change class, the results include objects from all Change subclasses (Deviation, ECO, ECR, MCO, PCO, SCO, and Stop Ship). If you want to search only a specific subclass, you should explicitly specify that class.

The following example shows how to create a query that searches for objects in a subclass named Foobar:

Example: Specifying the query class

    IAdmin admin = m_session.getAdminInstance();
    IAgileClass cls = admin.getAgileClass("Foobar");
    IQuery query = (IQuery)m_session.createObject(IQuery.OBJECT_TYPE, cls);

## 3.2 Saving a Query to a Folder

After you name a query using the IQuery.setName() method, you can add it to a folder. The following example shows how to name a query and add it to the Personal Searches folder. You can retrieve the query from the folder later to reuse it.

Example: Naming a query and adding it to a folder

    try {
    System.out.println(ex);

IQuery query =

(IQuery)session.createObject(IQuery.OBJECT_TYPE,

ItemConstants.CLASS_ITEM_BASE_CLASS);

query.setCaseSensitive(false);

query.setCriteria("[Title Block.Number] starts with 'P'");

query.setName("Items Whose Number Starts with P");

IFolder folder =

(IFolder)m_session.getObject(IFolder.OBJECT_TYPE,

"/Personal Searches");

folder.addChild(query);

    } catch (APIException ex) {
    }

You can also use the IQuery.saveAs() method to name a query and save it to a folder.

Example: Using IQuery.saveAs() to save a query to a folder

    try {
    System.out.println(ex);

IQuery query = (IQuery)session.createObject(IQuery.OBJECT_TYPE,

ItemConstants.CLASS_ITEM_BASE_CLASS);

query.setCaseSensitive(false);

query.setCriteria("[Title Block.Number] starts with 'P'");

IFolder folder = (IFolder)m_session.getObject(IFolder.OBJECT_TYPE, "/Personal Searches");

query.saveAs("Items Whose Number Starts with P", folder);

    } catch (APIException ex) {
    }

Note: Any query that you create without explicitly saving it to a folder is considered a temporary query. The Agile Application will automatically delete all temporary queries when the user session is closed.

## 3.3 Generating Ordered (sorted) or Unordered Query Results

As shown in examples for Creating a Query on page 39, executing IQuery.execute() and IQuery.execute(Object[] params) methods returns an ordered query result in ITable.

To improve query performance, the SDK provides the following methods to return results that are not sorted in the default order. However, if the query criteria has the starts with condition, then results are always sorted on that attribute and passing skipOrdering as true in execute(boolean) will not skip ordering.

Note: To sort query results by other than the default order, see Sorting Query Results on page 64.

- IQuery.execute(boolean skipOrdering)

- IQuery.execute(Object[] params, boolean skipOrdering)

To skip or perform ordering, set the boolean skipOrdering to true or false as shown in the following example.

Example: Skip ordering in query results

    try {
    IQuery query =
        (IQuery)session.createObject(IQuery.OBJECT_TYPE,

ItemConstants.CLASS_ITEM_BASE_CLASS);

    query.setCaseSensitive(false);
    query.setCriteria("[Title Block.Number] starts with 'P'");
      // The boolean is set to true to skip ordering
      ITable results = query.execute(true);
       } catch (APIException ex) {
        System.out.println(ex);
    }

## 3.4 Specifying Query Attributes when Creating a Query

Instead of passing only the query class when you create a query, you can use a more advanced form of the createObject() method and pass a Map object containing one or more attribute values. The QueryConstants class contains several constants for query attributes that you can set when you create a query. These are virtual attributes that do not exist in the Agile PLM database, but that can be used to define the query at run time.

Attribute Constant | Description
--- | ---
ATT_CRITERIA_CLASS | Query class
ATT_CRITERIA_PARAM | Search condition parameter value (for a parameterized search condition)
ATT_CRITERIA_STRING | Search condition string
ATT_PARENT_FOLDER | Parent folder where the query resides
ATT_QUERY_NAME | Query name

The following example shows how to set the query class, search condition, parent folder, and query name when you create the query.

Example: Specifying query attributes when you create a query

    try {
    String condition = "[Title Block.Number] starts with 'P'";

IFolder parent = (IFolder)m_session.getObject(IFolder.OBJECT_TYPE, "/Personal Searches");

HashMap map = new HashMap();

map.put(QueryConstants.ATT_CRITERIA_CLASS,

    ItemConstants.CLASS_ITEM_BASE_CLASS);
    } catch (APIException ex) {

map.put(QueryConstants.ATT_CRITERIA_STRING, condition);

map.put(QueryConstants.ATT_PARENT_FOLDER, parent);

map.put(QueryConstants.ATT_QUERY_NAME, "Part Numbers Starting with P");

IQuery query = (IQuery)m_session.createObject(IQuery.OBJECT_TYPE, map);

    ITable results = query.execute();

System.out.println(ex);

    }
    String condition = "[Title Block.Number] starts with %0 and" +

## 3.5 Specifying Search Criteria

You can narrow the number of objects returned from a search by specifying search criteria. If you don't specify search criteria, the query returns references to all objects in the specified query class. It's a good idea to limit the search criteria as much as possible, as the amount of data returned may be excessively large, resulting in decreased performance.

There are three different setCriteria() methods you can use to specify query criteria:

- setCriteria(ICriteria criteria) - Sets the query criteria from data stored in the Criteria administrative node. The Criteria administrative node defines reusable criteria for the Workflow, but the nodes can also be used as ordinary search criteria.

Note: Workflow query is not supported in the current Release of the SDK.

- setCriteria(java.lang.String criteria) - Sets the search criteria from a specified String.

- setCriteria(java.lang.String criteria, java.lang.Object[] params) - Sets the search criteria from a specified String that references one or more parameters.

Unless you use the first setCriteria() method, which takes an ICriteria object for its parameter, the Agile API parses the search criteria as a String.

### 3.5.1 Search Conditions

The Agile API provides a simple yet powerful query language for specifying search criteria. The query language defines the proper syntax for filters, conditions, attribute references, relational operators, logical operators, and other elements.

Search criteria consist of one or more search conditions. Each search condition contains the following elements:

1. Left operand - The left operand is always an attribute enclosed in brackets, such as [Title Block.Number]. You can specify the attribute as an attribute name (fully qualified name or short name) or attribute ID number. The attribute specifies which characteristic of the object to use in the search.

2. Relational operator - The relational operator defines the relationship that the attribute has to the specified value, for example, "equal to" or "not equal to."

3. Right operand - The matching value for the specified attribute in the left operand. The right operand can be a constant expression or a set of constant expressions. A set of constant expressions is needed if the relational operator is "between," "not between," "in," or "not in."

Following is an example of a search condition:

[Title Block.Description] == 'Computer'

This is another example where the right operand is a set of constant expressions:

[Page Two.Numeric01] between ('1000', '2000')

### 3.5.2 Query Language Keywords

When you specify a search condition, you must use proper keywords to construct the statement. The following keywords are available:

and | does | less | or | to
asc | equal | like | order | union
between | from | minus | phrase | where
by | greater | none | select | with
contain | in | not | start | word
contains | intersect | null | starts | words
desc | is | of | than | than

Query language keywords are not localized. You must use English keywords, regardless of locale. You can use the keywords in lower case or upper case. In addition to keywords, you can use Agile PLM variables such as $USER (for current user) and $TODAY (for today's date) in Agile API queries.

Note: The "in" operator does not support MultiList in (set) query criteria.

### 3.5.3 Specifying Search Attributes

Every Agile PLM object that you can search for also has an associated set of attributes, which are inherent characteristics of the object. You can use these attributes as the left operand of a search condition. The right operand of the search condition specifies the attribute's value(s).

A search attribute must be enclosed within brackets, for example, [Title Block.Number]. The brackets are needed because many attribute names have spaces. If a search attribute is not enclosed within brackets, your query will fail.

You can specify a search attribute in the following ways:

Attribute reference | Example
--- | ---
attribute ID number | [1001]
fully-qualified attribute name | [Title Block.Number]
short attribute name | [Number]

Note: Because attribute names can be modified, Agile recommends referencing attributes by ID number or constant. However, many of the examples in this chapter reference attributes by name simply to make them more readable. If you choose to reference attributes by name, use the fully-qualified attribute name instead of the short name. Short attribute names are not guaranteed to be unique and could therefore cause your query to fail or produce unexpected results.

Attribute names, whether you use the long or short form, are case-insensitive. For example, [Title Block.Number] and [TITLE BLOCK.NUMBER] are both allowed. Attribute names are also localized. The names of Agile PLM attributes vary based on the locale of your Agile Application Server. If you are creating a query that is going to be used on servers in different locales, you should reference attributes by ID number (or the equivalent constant) instead of by name.

Note: The APIName field, described in Accessing PLM Metadata with APIName Field on page 135, does not support specifying search attributes.

If the attribute name contains special characters, such as quotes or backslashes, you can type these characters using the backslash (\) as an escape character. For example, to include a quote character in your string, type \'. If you want to write a backslash, type two of them together (\\). If the attribute name contains square brackets, enclose the entire name in quotes:

    ['Page Two.Unit of Measure [g or oz]']
    query.setCriteria("[%0] == 'Computer'", new Object[] { attr });

There are other less intuitive ways to specify attributes. For example, you can pass in an IAttribute reference using a parameter of the setCriteria() method. In the following example, '%0' references the attribute in the Object array parameter.

You can also use String concatenation to reference an attribute constant:

    query.setCriteria("[" + ItemConstants.ATT_TITLE_BLOCK_DESCRIPTION + "] == 'Computer'");

### 3.5.4 Retrieving Searchable Attributes

The searchable attributes for a query depend on the specified query class or subclass. However, the searchable attributes for a subclass can differ greatly from searchable attributes for its parent class.

Due to database considerations, not all attributes are searchable. Generally, a few select Page One attribute (namely: Title Page, Cover Page, and General Info attributes) are searchable for each class.

If a tab is not configured in Java Client to be visible, you can still search for an attribute on that tab in the Agile SDK. However, you must search for the Table name that corresponds to the Tab name.

Note: Because you use the table name to setup IQuery, it does not matter if an Agile administrator changes a Tab name from the name specified in Agile Java Client. Tab name changes do not affect SDK table names.

To find the searchable attributes for a query, use the IQuery.getSearchableAttributes() method.

Note: Even though an attribute may not be searchable, it can still be included as a column in the query results. For more information, see Setting Result Attributes for a Query on page 57.

### 3.5.5 Retrieving the Sequence ID of a Query

Agile PLM generates sequence IDs for all saved queries. However, these query IDs are subject to change, because they are associated with new IDs after a Re-order. The following SDK calls get a query's sequence IDs before and after a Re-Order.

Example 3-1 Getting the Sequence ID of a Saved Query

    //Before Re-order, the id of one saved query QueryA is 1001. The SDK script is as below when we try to get the QueryA

    IQuery query = (IQuery) session.getObject(IQuery.OBJECT_TYPE, new Integer(1001));

Example 3-2 Getting the Sequence ID of a Query after the Re-Order

    After Re-order, the id of QueryA is changed. Assume it's changed to 102. Then, customer needs to modify the SDK script to get QueryA correctly

    IQuery query = (IQuery) session.getObject(IQuery.OBJECT_TYPE, new Integer(102));

### 3.5.6 Using Relational Operators

Table below lists relational operators that are supported by the Agile API query language.

English operator | Notation | Description
--- | --- | ---
equal to | == | Finds only an exact match with the specified value.
not equal to | != | Finds any value other than an exact match with the specified value.
greater than | > | Finds any value greater than the specified value.
greater than or equal to | >= | Finds any value greater than or equal to the specified value.
less than | < | Finds any value less than the specified value.
less than or equal to | <= | Finds any value less than or equal to the specified value.
contains, contains all |  |
does not contain, does not contain all |  |
contains any |  |
does not contain any |  |
contains none of |  |
does not contain none of |  |
starts with |  |
does not start with |  |
is null |  |
is not null |  |
like |  |
not like |  |
between |  |
not between |  |
in |  |
not in |  | Finds objects that do not match any of the specified values.
contains phrase |  |
contains all words |  | Finds objects with files that contain all of the specified words.
contains any word |  | Finds objects with files that contain any of the specified words.
contains none of |  |

Relational operators are not localized. You must use English keywords, regardless of locale. As with other query language keywords, you can use them in lower case or upper case.

#### 3.5.6.1 Using Unicode Escape Sequences

Agile SDK Query language supports Unicode escape sequences. The primary usage of Unicode escape sequences in a query string is to search for nonburnable or foreign local character sets. A Unicode character is represented with the Unicode escape sequence \uxxxx, where xxxx is a sequence of four hexadecimal digits.

For example, to search for an item with Unicode 3458, use the following query:

    Select * from [Items] where [Description] contains '\u3458'

There is another query operation for contains' usage in the case of MultiList.

#### 3.5.6.2 Using the Between, Not Between, In, and, Not In Operators

The 'between', 'not between', 'in', and 'not in' relational operators are not supported directly by Agile PLM Java and Web Clients. These relational operators provide a convenient shorthand method for specifying 'equal to', 'not equal to', 'greater than or equal to', or 'less than or equal to' operations with a set of values.

Short form | Equivalent long form
--- | ---
[Number] between ('1','6') | [Number] >= '1' and [Number] <= '6'
[Number] not between ('1','6') | [Number] < '1' and [Number] > '6'
[Number] in ('1','2','3','4',5','6') | [Number] == '1' or [Number] == '2' or [Number] == '3' or [Number] == '4' or [Number] == '5' or [Number] == '6'
[Number] not in ('1','2','3','4','5','6') | [Number]!= '1' and [Number] != '2' and [Number] != '3' and [Number] != '4' and [Number] != '5' and [Number] != '6'

As shown in the preceding table, when you use the 'between', 'not between', 'in', and 'not in' relational operators, each value in the set of values must be enclosed in quotes and delimited by commas. Here are more criteria examples that use 'between' and 'in' relational operators:

[Title Block.Number] in ('1000-02', '1234-01', '4567-89')

[Title Block.Effectivity Date] between ('01/01/2001', '01/01/2002')

[Page Two.Numeric01] between ('1000', '2000')

Note: The relational operators any, all, none of, and not all are not supported in the SDK.

#### 3.5.6.3 Using the Nested Criteria to Search for Values in Object Lists

Several lists in Agile PLM contain business objects, such as Agile PLM users. To search for an object in such a dynamic list, you can specify nested query criteria. Nested criteria are enclosed in parentheses and separated from each other by a logical AND (&&) or OR (||) operator. A comma can also be used to separate nested criteria; it's equivalent to a logical OR.

The following criteria find a user with the first name Christopher OR the last name Nolan.

[Page Two.Create User] in ([General Info.First Name] == 'Christopher',

[General Info.Last Name] == 'Nolan')

The following criteria find a user with the first name Christopher AND the last name Nolan.

[Page Two.Create User] in ([General Info.First Name] == 'Christopher' &&

[General Info.Last Name] == 'Nolan')

If Part.Page Three.List01 is enabled and set to Part Families list, the following criteria finds a Part Family with the name PartFamily_01

[Page Three.List01] in ([General Info.Name] == 'PartFamily_01')

The parameter query is not supported in nested queries and multiple values for one placeholder in query parameters must be specified in two dimensional arrays as shown in the example below.

Correct and incorrect parameter query in nested query criteria

- The parameter query specified in the following nested query criteria will fail to execute:

[Page Two.User1] in ([General Info.First Name] == %0)

- However, when it is explicitly specified as a string value, instead of the placeholder, it will succeed:

[Page Two.User1] in ([General Info.First Name] == 'Christopher')

#### 3.5.6.4 Using Criteria Selected from Criteria Library in SDK Queries

Criteria nodes in Java or Web Client's Criteria library are ICriteria objects that you can use in SDK queries. To view a listing in Java Client as shown below, select Admin > Settings > Data Settings > Criteria.

Figure 3-1 View a setting

The following example gets a Criteria node from the Criteria library and loads and sets it as the SDK query criteria.

Example: Using criteria from the Criteria Library in SDK queries

    IQuery query = (IQuery) session.createObject(IQuery.OBJECT_TYPE, ItemConstants.CLASS_ITEM_BASE_CLASS);
    // Get the Criteria Library node

IAdmin admin = session.getAdminInstance();

INode criteriaLibrary = admin.getNode(NodeConstants.NODE_CRITERIA_LIBRARY);

    // Load the Criteria relevant to the query class (For example it is Items base class)
    // Set the ICriteria  in SDK Query Criteria

ICriteria criteria = (ICriteria) criteriaLibrary.getChild("All Released Items");

query.setCriteria(criteria);

#### 3.5.6.5 Using Relationships and Content in SDK Queries

Agile SDK provides APIs to perform the Relationships and Content Search using the IQuery interface. The query criteria can contain the attributes of both the base search class and the related class.

To search using an object's Relationships:

1. Set searchType to QueryConstants.RELATIONSHIPS using IQuery.setSearchType(int searchType).

2. Set the related class using IQuery.setRelatedContentClass(Object relatedClass).

Example: Using an object's Relationships as the query criteria

    IQuery query1 = (IQuery) session.createObject(IQuery.OBJECT_TYPE, ItemConstants.CLASS_PART);

query1.setSearchType(QueryConstants.RELATIONSHIPS);

query1.setRelatedContentClass("Substance"); // ID or API Name

query1.setCriteria("[Relationships.Name] Is Not Null and [Title Block.Number] equals 'P00001' and [Relationships.Substance.General Info.Name] Is Not Null");

To search using a Project object's Content:

1. Set searchType to QueryConstants.RELATIONSHIPS using IQuery.setSearchType(int searchType).

2. Set the related class using IQuery.setRelatedContentClass(Object relatedClass).

Example: Using a Project object's Content as the query criteria

    IQuery query1 = (IQuery) session.createObject(IQuery.OBJECT_TYPE, ProgramConstants.CLASS_ACTIVITIES_CLASS);

query1.setSearchType(QueryConstants.RELATIONSHIPS);

query1.setRelatedContentClass("ECO"); // ID or API Name

query1.setCriteria("[Content.Criteria Met] Is Not Null and [Content.ECO.Cover Page.Originator] in ([General Info.First Name] == 'admin')

To search using a Transfer Orders object's Selected Content:

1. Set searchType to QueryConstants.TRANSFER_ORDER_SELECTED_CONTENT using IQuery.setSearchType(int searchType).

2. Set the related class using IQuery.setRelatedContentClass(Object relatedClass).

Example: Using a Transfer Orders object's Selected Content as the query criteria

    IQuery query1 = (IQuery) session.createObject(IQuery.OBJECT_TYPE, TransferOrderConstants.CLASS_CTO);

query1.setSearchType(QueryConstants.TRANSFER_ORDER_SELECTED_CONTENT);

query1.setRelatedContentClass("ECR"); // ID or API Name

query1.setCriteria("[Selected Content.ECR.Cover Page.Number] equal to 'C0001'");

#### 3.5.6.6 Searching for Words or Phrases Contained in Attachments

Two special attributes, [Attachments.File Document Text] and [Files.Document Text] are used to index files and search for files indexed by File Manager that reside on the Agile file management server. If you are hosting your database on Oracle, you can take advantage of a feature that lets you search for words or phrases contained in attachments, when you create search criteria that use either of these attributes.

There are four additional relational operators that you can use:

- contains phrase

- contains all words

- contains any word

- contains none of

The following table shows several search conditions that search for words or phrases in attachments.

Search Condition | Finds
--- | ---
[Attachments.File Document Text] contains phrase 'adding new materials' | Objects in which any of their attachments contain the phrase "adding new materials."
all [Attachments.File Document Text] contains all words 'adding new materials' | Objects in which all their attachments contain the words "adding," "new," and "materials."
none of [Attachments.File Document Text] contains any word 'containers BOM return output' | Objects in which none of their attachments contain any of the words "containers," "BOM," "return," or "output."
[Attachments.File Document Text] contains none of 'containers BOM output' | Objects in which any of their attachments do not contain the words "containers," "BOM," or "output."

#### 3.5.6.7 Searching for Orphaned Parts (Parts without Parents)

Orphaned parts are parts that do not have parents. This arises from the notion that in a BOM Tree, there are Parents and Children. When the Child part no longer has any Parents, it is an Orphan part. SDK Provides the following two attributes to enable retrieving Orphan Parts:

- Where Used.Item Number[1039] - The null value returns the current revision having no parents

Where [1039] is null

- Where Used.Item Number All Revision - The null value returns all revision having no parents

Where [20W00025272] is null

The following code sample uses these attributes to retrieve Orphan parts in the current version and all versions of the BOM.

Example: Searching for Orphan parts

System.out.println("Where Used.Item Number[1039] = " +ItemConstants.ATT_WHERE_USED_ITEM_NUMBER);

System.out.println("Where Used.Item Number All Revision[2000025272] =

" +ItemConstants.ATT_WHERE_USED_ITEM_NUMBER_ALL_REVISIONS);

IAgileSession session = AgileSessionFactory.createSessionEx(params);

IQuery query =(IQuery)session.createObject(IQuery.OBJECT_TYPE,ItemConstants.CLASS_PARTS_CLASS);

query.setCaseSensitive(false);

query.setCriteria("[1039] is null");

    //query.setCriteria("[2000025272] is null");

ITable results = query.execute();

System.out.println("result size:"+results.size());

### 3.5.7 Creating a Parameterized Query

When you specify criteria for a query, you can use a number preceded by a percent sign (%) to indicate a parameter placeholder. The parameter value is specified later, for example at runtime. Parameters provide a convenient way to pass values to a query, and they can save time and reduce extra coding. Parameterized queries can be saved and reused later.

Note: The right hand operand query parameter supports one placeholder per each query operator, so if the query criteria have three query operators, then the query can have a total of three placeholders corresponding to the three operators. The between and not between query operations are different. For example, [2091] contains none of (%0,%1);is not allowed, but [2091] contains none of (%0); is allowed, and query.execute(new Object[]{new Object[]{"B", "C"}}); is not allowed.

Indexes for query parameters are 0-based. Parameters are numbered 0, 1, 2, and so on. Always enumerate the parameters in ascending order. The following example shows a query with three parameters whose values are specified using the IQuery.execute(Object[]) method.

Example: Parameterized query using IQuery.execute(Object[])

    public ITable runParameterizedQuery() throws Exception {
                         "[Title Block.Part Category] == %1 and" +

"[Title Block.Description] contains %2";

    IQuery query = (IQuery)m_session.createObject(IQuery.OBJECT_TYPE,
    query.setCriteria("[Title Block.Rev Release Date] between" +

ItemConstants.CLASS_PART);

query.setCriteria(condition);

ITable table = query.execute(new Object[] {"1", "Electrical", "Resistor"});

return table;

    }

You can also specify query parameters using IQuery.setParams() method, as shown in the following example. Make sure you set the query parameter values before calling IQuery.execute(). Otherwise, when you run the query it will use previous parameter values. If parameters have not been set, the query uses null values. Similarly, if you do not pass any parameters to a query, then the IQuery.getParams() method returns null.

Example: Parameterized query using IQuery.setParams()

    public ITable runParameterizedQuery() throws Exception {

String condition = "[Title Block.Number] starts with %0 and" +

"[Title Block.Part Category] == %1 and" +

"[Title Block.Description] contains %2";

    IQuery query =

IQuery) m_session.createObject(IQuery.OBJECT_TYPE, ItemConstants.CLASS_PART);

query.setCriteria(condition);

query.setParams(new Object[] {"1", "Electrical", "Resistor"});

ITable table = query.execute();

return table;

    }

Do not use quote characters around parameterized queries because they will create a set of values (more than one element) for the query when parameters can only refer to a given value. The following examples show the proper use of quote characters when creating parameterized queries:

Example: Correct use of quote characters in a parameterized search query

String criteria = "[NUMBER] == %0";

query.execute(new Object[]{"P1000-02"});

String criteria = "[P2.LIST01] in %0";

query.execute(new Object[]{new Object[]{"A1", "B2"}});

### 3.5.8 Formatting Dates in Query Criteria

Several types of queries require date values. To pass a date as a String, use the IAgileSession.setDateFormats() method to specify a date format. The setDateFormats() method also applies to all Agile API values that you specify with setValue() methods.

Note If you do not set date formats explicitly using the setDateFormats() method, the Agile API uses the user's date format for the Agile PLM system. To see your date format in Agile Web Client, choose Settings > User Profile and then click the Preferences tab.

Example: Setting the date format for a query

    m_session.setDateFormats(new DateFormat[] {new SimpleDateFormat("MM/dd/yyyy")});
    "('9/2/2001', '9/2/2003')");
    query.setCriteria("[Title Block.Rev Release Date]between (%0,%1)", new String[] {"9/2/2001", "9/2/2003"} );

Alternatively, if you use the setCriteria(String criteria, Object[] params) method, you can pass Date objects as parameters to the method.

Example: Passing Date objects as parameters of setCriteria()

    DateFormat df = new SimpleDateFormat("MM/dd/yyyy");
    query.setCriteria("[Title Block.Rev Release Date] between (%0,%1)",
    new Object[] { df.parse("9/2/2001"), df.parse("9/2/2003") });

### 3.5.9 Using Logical Operators

You can use logical operators to combine multiple search conditions into a complex filter. When you have two or more conditions defined in a set of query criteria, the relationship between them is defined as either 'and' or 'or'.

- and narrows the search by requiring that both conditions are met. Each item in the results must match both conditions. The 'and' logical operator can also be specified using two ampersands, '&&'.

- or broadens the search by including any object that meets either condition. Each item in the results table needs to match only one of the conditions, but may match both. The 'or' logical operator can also be specified using two vertical bars, '||'.

Logical operators are case-insensitive. For example, 'and' or 'AND' are both allowed.

The following query criteria finds parts that have both a part category equal to Electrical and a lifecycle phase equal to Inactive.

[Title Block.Part Category] == 'Electrical' and

[Title Block.Lifecycle Phase] == 'Inactive'

If you replace the 'and' operator with 'or', the query locates all parts with either a part category of Electrical or a lifecycle phase of Inactive, which could be a large number of parts.

[Title Block.Part Category] == 'Electrical'

or

[Title Block.Lifecycle Phase] == 'Inactive'

Note The Agile API provides three where-used set operators. For more information, see Creating a Where-Used Query on page 52. Logical operators, including the where-used set operators, are not localized. You must use English keywords, regardless of locale.

### 3.5.10 Using Wildcard Characters with the Like Operator

If you define a search condition using the 'like' operator, you can use two wildcard characters: the asterisk (*) and question mark (?). The asterisk matches any string of any length, so *at finds cat, splat, and big hat. For example, [Title Block.Description] like '*book*' returns all objects that contain the word "book," such as textbook, bookstore, books, and so on.

The question mark matches any single character, so ?at finds hat, cat, and fat, but not splat. For example, [Title Block.Description] like '?al*' matches any word containing "al" that is preceded by a single letter, such as tall, wall, mall, calendar, and so on.

### 3.5.11 Using Parentheses in Search Criteria

Where-used, set operators have higher priority than and and or logical operators, as shown by the following table.

Priority | Operator(s)
--- | ---
1 | union intersection minus
2 | and or

Therefore, search conditions joined by union, intersection, and minus operators are evaluated before conditions joined by and or or.

If you use where-used set operators ('union', 'intersect', or 'minus') in search criteria, you can use parentheses to change the order that criteria are evaluated. If only 'and' or 'or' logical operators are used in a search criteria, additional parentheses aren't needed because they do not change the result of criteria evaluation.

The following two criteria, although they contain the same search conditions, provide different results because parentheses are placed differently:

([Title Block.Part Category] == 'Electrical' and

[Title Block.Description] contains 'Resistor') union

([Title Block.Description] contains '400' and

[Title Block.Product Line(s)] contains 'Taurus')

[Title Block.Part Category] == 'Electrical' and

([Title Block.Description] contains 'Resistor' union

[Title Block.Description] contains '400') and

[Title Block.Product Line(s)] contains 'Taurus'

### 3.5.12 Setting Search Criteria for Lists Containing Large Numbers of Objects

When using the SDK to query lists that contain a large number of objects, you can improve performance if you use the object ID in the query criteria to set the value for the list

For example, you can replace this routine:

query.setCriteria("[Page Three.List25] equal to 'Administrator (admin)'");

with the following for better performance:

IUser user = (IUser)session.getObject(IUser.OBJECT_TYPE, "admin");

query.setCriteria("[Page Three.List25] equal to "+user.getObjectId());

## 3.6 Using SQL Syntax for Search Criteria

In addition to its standard query language, the Agile API also supports SQL-like syntax for search criteria. If you're familiar with how to write SQL statements, you may find this extended query language easier to work with, more flexible, and more powerful. It combines in one operation the specification of the query result attributes, the query class, the search condition, and the sort column(s).

This is a simple example that demonstrates the syntax:

- Query result attributes: SELECT [Title Block.Number], [Title Block.Description]

- Query class: FROM [Items]

- Search condition: WHERE [Title Block.Number] starts with 'P'

- Sort column(s): ORDER BY 1 asc

To improve readability, it's recommended that SQL key words such as SELECT and FROM are all typed using capital letters and each part of the statement appears on a separate line. This is merely a convention, not a requirement. SQL key words are not case-sensitive, and you can write the entire query string on one line if you prefer.

The best way to demonstrate the advantages of SQL syntax is to compare the code for a query that uses standard Agile API query syntax for search criteria with one that uses SQL syntax. The following example shows a query created using the standard Agile API query syntax:

Example: Query using standard Agile API query syntax

    try {
    //Run the query

IQuery query = (IQuery)m_session.createObject(IQuery.OBJECT_TYPE, "Items");

query.setCriteria("[Page Two.Nummeric01] between (1000, 2000)");

    //Set result attributes

String[] attrs = { "Title Block.Number", "Title Block.Description",

"Title Block.Lifecycle Phase" };

query.setResultAttributes(attrs);

//Run the query

ITable results = query.execute();

} catch (APIException ex) {

System.out.println(ex);

}

This example shows the same query rewritten in SQL syntax. Although the example doesn't have fewer lines of code, you may find that it's more readable than Agile API query syntax, particularly if you're familiar with SQL.

Example: Query using SQL syntax

    try {

IQuery query = (IQuery)m_session.createObject(IQuery.OBJECT_TYPE,

"SELECT " +

"[Title Block.Number],[Title Block.Description], " +

"[Title Block.Lifecycle Phase] " +

"FROM " +

    "[Items] " +

"WHERE " +

"[Title Block.Number] between (1000, 2000)"

    );

ITable results = query.execute();

} catch (APIException ex) {

    System.out.println(ex);
    }

The following example shows a query written with SQL syntax that specifies the search criteria using the ATT_CRITERIA_STRING query attribute. For more information about how to use query attributes, see Specifying Query Attributes when Creating a Query.

Example: Using SQL syntax to specify query attributes

    try {
    // Get Admin instance

String statement =

"SELECT " +

"[Title Block.Number], [Title Block.Description] " +

"FROM " +

"[Items] " +

"WHERE " +

"[Title Block.Description] like %0";

HashMap map = new HashMap();

map.put(QueryConstants.ATT_CRITERIA_STRING, statement);

map.put(QueryConstants.ATT_CRITERIA_PARAM, new Object[] { "Comp*" } );

IQuery query = (IQuery)m_session.createObject(IQuery.OBJECT_TYPE, map);

ITable results = query.execute();

} catch (APIException ex) {

System.out.println(ex);

    }

Note: Remember, the FROM part of the search condition specifies the query class. If you use the ATT_CRITERIA_CLASS attribute to also specify a query class, the query class specified in the SQL search condition takes precedence.

Although you can use the IQuery.setCriteria() method to specify a search condition in SQL syntax, the IQuery.getCriteria() method always returns the search condition in the standard Agile API query syntax.

### 3.6.1 Using SQL Wildcards

You can use both the asterisk (*) and question mark (?) wildcards in a query that uses SQL syntax. As in standard Agile API query language, the asterisk matches any string and the question mark matches any single character. You can use wildcards in the SELECT statement (the specified query result attributes) and the WHERE statement (the search condition). For example, "SELECT *" specifies all available query result attributes.

### 3.6.2 Sorting Query Results Using SQL Syntax

If you specify search criteria using SQL syntax instead of the standard Agile API query language, you can use the ORDER BY keyword to sort the query results. You can sort the results in ascending or descending order by any attributes specified in the SELECT statement.

In the ORDER BY statement, refer to attributes by the one-based numerical order in which they appear in the SELECT statement. To specify whether to sort in ascending or descending order, type "asc" or "desc" after the attribute number. If "asc" or "desc" is omitted, ascending order is used by default.

Example | Description
--- | ---
ORDER BY 1 | Sort by the first SELECT attribute in ascending order (the default)
ORDER BY 2 desc | Sort by the second SELECT attribute in descending order
ORDER BY 1 asc, 3 desc | Sort by the first SELECT attribute in ascending order and the third SELECT attribute in descending order

Attributes not specified in the SELECT statement cannot be used to sort query results. Also, if you use "SELECT *" to select all available result attributes, the results cannot be sorted because the attribute order is undefined.

The following example sorts results in ascending order by [Title Block.Number] and [Title Block.Sites], the first and third attributes in the SELECT statement.

Example: Using SQL syntax to sort query results

    IQuery query = (IQuery)m_session.createObject(IQuery.OBJECT_TYPE,

"SELECT " +

"[Title Block.Number],[Title Block.Description], " +

"[Title Block.Sites],[Title Block.Lifecycle Phase] " +

"FROM " +

"[Items] " +

"WHERE " +

"[Title Block.Number] between (1000, 2000)" +

"ORDER BY " +

"1, 3"

);

## 3.7 Setting Result Attributes for a Query

When you run a query, it returns several output fields, which are also called result attributes. By default, there are only a few result attributes for each query class. You can add or remove result attributes using the IQuery.setResultAttributes() method.

The following table shows the default query result attributes for each predefined Agile PLM class.

Query class | Default result attributes
--- | ---
Changes Change Orders ECO Change Requests ECR Deviations Deviation Manufacturer Orders MCO Price Change Orders PCO Sites Change Orders SCO Stop Ships Stop Ship | Cover Page.Change Type Cover Page.Number Cover Page.Description Cover Page.Status Cover Page.Workflow
Customers Customers Customer | General Info.Customer Type General Info.Customer Number General Info.Customer Name General Info.Description General Info.Lifecycle Phase
Declarations Homogeneous Material Declarations Homogeneous Material Declaration IPC 1752-1 Declarations IPC 1752-1 Declaration IPC 1752-2 Declarations IPC 1752-2 Declaration JGPSSI Declarations JGPSSI Declaration Part Declarations Part Declaration Substance Declarations Substance Declaration Supplier Declarations of Conformance Supplier Declaration of Conformance | Cover Page.Name Cover Page.Description Cover Page.Supplier Cover Page.Status Cover Page.Workflow Cover Page.Compliance Manager Cover Page.Due Date Cover Page.Declaration Type
Discussions Discussions Discussion | Cover Page.Subject Cover Page.Status Cover Page.Priority Cover Page.Type
File Folders File Folders File Folder | Title Block.Type Title Block.Number Title Block.Description Title Block.Lifecycle Phase
Items Parts Part Documentation Document | Title Block.Item Type Title Block.Number Title Block.Description Title Block.Lifecycle Phase Title Block.Rev
Manufacturers Manufacturers Manufacturer | General Info.Name General Info.City General Info.State General Info.Lifecycle Phase General Info.URL
Manufacturer Parts Manufacturer Parts Manufacturer Part | General Info.Manufacturer Part Number General Info.Manufacturer Name General Info.Description General Info.Lifecycle Phase
Packages Packages Package | Cover Page.Package Number Cover Page.Description Cover Page.Assembly Number Cover Page.Status Cover Page.Workflow
Part Groups Part Groups Commodity Part Family | General Info.Name General Info.Description General Info.Lifecycle Phase General Info.Commodity Type General Info.Overall Compliance
Prices Published Prices Contracts Published Price Quote History Quote History | General Info.Price Number General Info.Description General Info.Rev General Info.Price Type General Info.Lifecycle Phase General Info.Projects General Info.Customer General Info.Supplier
Product Service Requests Non-Conformance Reports NCR Problem Reports Problem Report | Cover Page.PSR Type Cover Page.Number Cover Page.Description Cover Page.Status Cover Page.Workflow
Projects Activities Phase Program Project Task Gates Gate | General Info.Name General Info.Description General Info.Status General Info.Health General Info.Owner General Info.Root Parent General Info.Workflow General Info.Type
Sourcing Projects Sourcing projects Sourcing Projects | General Info.Project Type General Info.Number General Info.Description General Info.Manufacturing Site General Info.Ship To Location General Info.Projects General Info.Customer General Info.Lifecycle Phase
Quality Change Requests Corrective Action/Preventive Action CAPA Audits Audit | Cover Page.QCR Type Cover Page.QCR Number Cover Page.Description Cover Page.Status Cover Page.Workflow
RFQ Responses RFQ Responses RFQ Response | Cover Page.RFQ Number Cover Page.RFQ Description Cover Page.Lifecycle Phase Cover Page.Requested Cover Page.Completed Cover Page.Due Date
RFQs RFQs RFQ | Cover Page.RFQ Number Cover Page.RFQ Description Cover Page.MFG Site Cover Page.Ship-To Location Cover Page.Projects Cover Page.Customer Cover Page.Lifecycle Phase Cover Page.RFQ Type
Sites Sites Site | General Info.Name General Info.Contact General Info.Phone
Specifications Specifications Specification | General Info.Name General Info.Description General Info.Lifecycle Phase General Info.Jurisdictions General Info.Validation Type General Info.Specification Type
Substances Materials Material Subparts Subpart Substance Groups Substance Group Substances Substance | General Info.Name General Info.Description General Info.CAS Number General Info.Lifecycle Phase General Info.Substance Type
Suppliers Suppliers Component Manufacturer Contract Manufacturer Distributor Manufacturer Rep | General Info.Supplier Type General Info.Number General Info.Name General Info.Description General Info.Status
Transfer Orders Content Transfer Orders CTO Automated Transfer Orders ATO | Cover Page.Transfer Order Type (See Retrieving CTO Originator Name on page 64 Cover Page.Transfer Order Number Cover Page.Description Cover Page.Status Cover Page.Workflow

### 3.7.1 Specifying Result Attributes

If you run a query and find that the resulting ITable object does not contain the attributes you expected, it's because you didn't specify result attributes. The following example shows how to specify the result attributes for a query.

Example: Setting query result attributes

    private void setQueryResultColumns(IQuery query) throws APIException {

IAdmin admin = m_session.getAdminInstance();

    // Get the Part class
    public static void testSearchBasedOn() throws Exception {
    // Get some Part attributes, including Page Two and Page Three attributes

IAgileClass cls = admin.getAgileClass("Part");

IAribute attr1 = cls.getAttribute(ItemConstants.ATT_TITLE_BLOCK_NUMBER);

IAribute attr2 = cls.getAttribute(ItemConstants.ATT_TITLE_BLOCK_DESCRIPTION);

IAribute attr3 = cls.getAttribute(ItemConstants.ATT_TITLE_BLOCK_LIFECYCLE_PHASE);

IAttribute attr4 = cls.getAttribute(ItemConstants.ATT_PAGE_TWO_TEXT01);

IAttribute attr5 = cls.getAttribute(ItemConstants.ATT_PAGE_TWO_NUMERIC01);

IAttribute attr6 = cls.getAttribute(ItemConstants.ATT_PAGE_THREE_TEXT01);

    // Put the attributes into an array
    // Set the result attributes for the query

IAttribute[] attrs = {attr1, attr2, attr3, attr4, attr5, attr6};

query.setResultAttributes(attrs);

    }

The IQuery.setResultAttributes() method takes an Object[] value for the attrs parameter, supporting String, Integer,, or IAttribute arrays. Therefore, instead of specifying an array of IAttribute objects, you can also specify an array of attribute names (such as {"Title Block.Description", "Title Block.Number"} ) or attribute ID constants. The following example shows how to specify result attributes using ID constants.

Example: Setting query result attributes by specifying ID constants

    private void setQueryResultColumns(IQuery query) throws APIException {
    // Put the attribute IDs into an array

Integer[] attrs = { ItemConstants.ATT_TITLE_BLOCK_NUMBER,

ItemConstants.ATT_TITLE_BLOCK_DESCRIPTION,

ItemConstants.ATT_TITLE_BLOCK_LIFECYCLE_PHASE,

ItemConstants.ATT_PAGE_TWO_TEXT01,

ImConstants.ATT_PAGE_TWO_NUMERIC01,

ItemConstants.ATT_PAGE_THREE_TEXT01 };

    // Set the result attributes for the query
    }

query.setResultAttributes(attrs);

When you use the setResultAttributes() method, make sure you specify valid result attributes. Otherwise, the setResultAttributes() method will fail. To get an array of available result attributes that can be used for a query, use getResultAttributes(), as shown in the following example.

Example: Getting the array of available result attributes

    private IAttribute[] getAllResultAttributes(IQuery query) throws APIException {

IAttribute[] attrs = query.getResultAttributes(true);

return attrs;

    }

### 3.7.2 Retrieving CTO Originator Name

The Cover Page of the Content Transfer Order (CTO) includes the Originator field which specifies roles and site assignments of users who originate CTOs. To retrieve the user name, you can not query this field directly and need to retrieve data in UserConstants. For example, the following statement which attempts to retrieve the user name directly, will not work:

QueryString = ("[Cover Page.Originator] equal to '<Last_name>, <First_name>'");

But the following statements which also specify the data in UserConstants will work:

QueryString = "[Cover Page.Originator] in (["+UserConstants.ATT_GENERAL_INFO_USER_ID+"]=='<UserID>')";

Or,

QueryString = "[Cover Page.Originator] in (["+UserConstants.ATT_GENERAL_INFO_LAST_NAME+"]=='<Last_name>'"+

"&&

["+UserConstants.ATT_GENERAL_INFO_FIRST_NAME+"]=='<First_name>');

The query criteria for any innumerable attribute type such as IItem, IChange, and so on, must be in a nested form. This applies to the Originator attribute which points to Agile All users.

### 3.7.3 Duplicate Results for Site-Related Objects and AMLs

The manufacturing sites functionality of the Agile Application Server can have unintended results when you search for items or changes. If you search for items or changes and include a sites attribute-[Title Block.Site] for items and [Cover Page.Site(s)] for changes-in the result attributes, the query results include duplicate objects for each site associated with the object. Similarly, if you search for items and include an AML attribute-such as [Manufacturers.Mfr. Part Number]-in the result attributes, the query results include duplicate items for each manufacturer part listed on an item's Manufacturers table.

For example, a part with the number 1000-02 has five sites associated with it. If you search for that part and include Title Block.Site in the result attributes, the resulting ITable object returned by the IQuery.execute method contains five rows, not one. Each row references the same object, part number 1000-02, but the Site cell has a different value. If you use ITable.getReferentIterator to iterate through referenced objects in the search results, the duplicate objects would be more apparent; in this example, you would iterate over the same item five times.

## 3.8 Working with Query Results

When you run a query, the Agile API returns an ITable object, which extends java.Util.Collection. You can use the methods of ITable and of java.Util.Collection to work with the results. For example, the following code shows how to use the Collection.iterator() method.

Iterator it = query.execute().iterator();

The ITwoWayIterator interface lets you traverse the list of rows in either direction using the next() and previous() methods.

ITwoWayIterator it = query.execute().getTableIterator();

ITwoWayIterator it = query.execute().getReferentIterator();

For more information about using ITwoWayIterator, see Iterating Over Table Rows on page 83.

### 3.8.1 Sorting Query Results

Unlike other Agile API tables, you cannot create a sorted iterator for query results using the ITable.ISortBy interface. To sort query results, use SQL syntax and specify an ORDER BY statement with the search criteria. For more information, see Using SQL Syntax for Search Criteria on page 54.

### 3.8.2 Query Result Datatypes

Values in a query results table have the same datatype as their attributes. That is, if an attribute's datatype is an Integer, its value in a query results table is also an Integer.

Important: Remember that in Agile 9.0 SDK, all values in a query results table are strings. In post Agile 9.2, these values are integers.

### 3.8.3 Using Admin Preferences Attributes to Manage Queries and Reports

Using PLM's Java Client, a user with Admin privileges can set the following Administrator Preferences by selecting Admin > Server Settings > Preferences and any one of these or other supported Preference. You can also set the above three Admin Preferences attributes from the SDK.

- Maximum Query Results Displayed

- Maximum BOM Reports Results

- Search Based on (Table or Row)

For more information about Admin Preferences (Systemwide Preferences), settings, and returned values, refer to Agile PLM Administrator Guide. For SDK-related descriptions and procedures, see Setting Maximum Query Results Displayed on page 66, Setting Maximum BOM Report Results on page 66, and Selecting Search Results Based on Table or Row on page 67.

#### 3.8.3.1 Setting Maximum Query Results Displayed

This preference sets a limit on the maximum number of rows that are returned by a query and displayed on the monitor. However, this preference does not affect Agile PLM Clients. Queries that you run from an Agile SDK Client always return all results. That is, although you can access the entire query result set with the returned ITable object, the Agile API internally manages retrieving partial results when necessary. For example, if a particular query returns 5000 records, you can use the ITable interface to access any of these 5000 rows, regardless of how many of the 5000 rows the Agile API actually loaded into memory.

Note: Searches that you run from other Agile PLM Clients, such as Agile Web Client, adhere to the limit set in the Maximum Query Results Displayed preference.

#### 3.8.3.2 Setting Maximum BOM Report Results

This Admin Preference is based on the Maximum Report Results attribute which determines the maximum number of objects displayed in Agile PLM Custom Reports. This preference does not apply to Standard Reports and is overridden by the Full Search Display (FSD) privilege. For more information, refer to Agile PLM Administrator Guide.

Note: Users with the FSD privilege see all results of reports; also, all privilege checking is bypassed on users with this privilege when they view report results. Users without the FSD privilege see the maximum number of reports specified in this property.

This Preference uses the Property.Constants.PROP_MAXIMUM_BOM_REPORT_RESULTS attribute to set a limit on the maximum number of BOM Report Results returned.

Example: Setting Property.Constants.PROP_MAXIMUM_BOM_REPORT_RESULTS values /* This example sets the maximum BOM Report Results to 666 */

    public static void testMaxBomReportResults() throws Exception {

INode preferences =

session.getAdminInstance().getNode(NodeConstants.NODE_PREFERENCES);

prop =

preferences.getProperty(PropertyConstants.PROP_MAXIMUM_BOM_REPORT_RESULTS);

Object original = prop.getValue();

System.out.println("Current MaxBomReportResults value:" + original); prop.setValue("666");

System.out.println("Changed MaxBomReportResults to:" + prop.getValue());

    }

#### 3.8.3.3 Selecting Search Results Based on Table or Row

This preference uses the PropertyConstants.PROP_SEARCH_BASED_ON attribute to set the search based on Property, where Property is a list and valid values are "Table" and "Row."

Example: Setting PropertyConstants.PROP_SEARCH_BASED_ON Table or Row

    /* This example sets the Search Based on Table */

INode preferences = session.getAdminInstance().getNode(NodeConstants.NODE_PREFERENCES);

prop =

preferences.getProperty(PropertyConstants.PROP_SEARCH_BASED_ON);

Object original = prop.getValue();

System.out.println("Current Search Based On value:" + original);

IAgileList list = prop.getAvailableValues();

// Valid values are "Table" and "Row"

list.setSelection(new Object[] {"Table"});

prop.setValue(list);

System.out.println("Changed Search Based On property to:" + prop.getValue());

    }
    //Get the Workflow Routings folder

### 3.8.4 Query Performance

The response time for running queries can be the biggest bottleneck in your Agile API program. To improve performance, you should try to construct queries that return no more than a few hundred results. A query that returns more than a 1000 results can take several minutes to finish processing. Such queries also eat up valuable processing on the Agile Application Server, potentially slowing down your server for all users.

## 3.9 Creating a Where-Used Query

Previous sections of this chapter described how to create queries that search for Agile PLM objects, for example, items or changes. You can also create where-used queries. In a where-used query, the search conditions define the items that appear on the BOMs of objects. You can use a where-used query to find the assemblies on which a particular part is used.

The interface for a where-used query is similar to a standard object query. With minor changes, you can turn an object query into a where-used query as long as the query class is an Item class.

Note: Where-used queries are only defined for Item classes.

To define a where-used query, use the IQuery.setSearchType() method. You can also use the following logical operators, also called where-used set operators, to further define the relationships between grouped sets of search conditions. Only one logical operator can be used for each search condition.

Where Used set operator | Description
--- | ---
intersect | Produces records that appear in both result sets from two different groups of search conditions.
minus | Produces records that result from the first group of search conditions but not the second.
union | Produces records that are the combination of results from two groups of search conditions.

Note: Where-used set operators have higher priority than other logical operators. Therefore, search conditions joined by where-used set operations are evaluated before those joined by 'and' or 'or' operators.

Example: Where-used query

    void btnFind_actionPerformed(ActionEvent e) {
    // Create the query

try {

IQuery wuquery =

(IQuery)m_session.createObject(IQuery.OBJECT_TYPE, ItemConstants.CLASS_ITEM_BASE_CLASS);

    // Set the where-used type
    // Add query criteria

wuquery.setSearchType(QueryConstants.WHERE_USED_ONE_LEVEL_LATEST_RELEASED);

wuquery.setCriteria(

"[Title Block.Part Category] == 'Electrical'" +

"and [Title Block.Description] contains 'Resistor'" +

"union [Title Block.Description] contains '400'" +

"and [Title Block.Product Line(s)] contains 'Taurus'");

    // Run the query
    // Add code here to display the results

ITable results = wuquery.execute();

}

catch (APIException ex) {System.out.println(ex);}

    }

## 3.10 Loading a Query

There are two ways to load a query:

- Use the IAgileSession.getObject() method to specify the full path of a query.

- Use the IFolder.getChild() method to specify the location of a query relative to a folder.

The following example shows how to load a query by specifying its full path.

Example: Loading a query using IAgileSession.getObject()

    try {
    //Load the "Changes Submitted to Me" query

IQuery query =

(IQuery)m_session.getObject(IQuery.OBJECT_TYPE,"/Workflow Routings/Changes Submitted To Me");

    } catch (APIException ex) {
    }

System.out.println(ex);

The following example shows how to load a query by specifying its path relative to a folder, in this case the user's Public In-box folder.

Example: Loading a query using IFolder.getChild()

    try {

IFolder folder =

(IFolder)m_session.getObject(IFolder.OBJECT_TYPE, "/Workflow Routings");

    //Load the "Changes Submitted to Me" query

IQuery query =

(IQuery)folder.getChild("Changes Submitted To Me");

    } catch (APIException ex) {
    }

System.out.println(ex);

## 3.11 Deleting a Query

To delete a saved query, use the IQuery.delete() method. Temporary queries, that is, queries that are created but not saved to a folder are automatically deleted after the user session is closed. For lengthy sessions, you can use the delete()method to explicitly delete a temporary query after you're finished running it.

Example: Deleting a query

    void deleteQuery(IQuery query) throws APIException {
    }

query.delete();

## 3.12 Simple Query Examples

Figure 3-2 An example of dialog box that performs a simple query.

The Simple Query dialog box lets the user specify an item number to search for. When the user clicks the Find button, the program constructs a query to find all items that contain the specified text in the Item Number field. This example shows the code that runs the query when the user clicks the Find button.

Example: Simple Query code

    void btnFind_actionPerformed(ActionEvent e) {
    // Create the query

try {

IQuery query =

(IQuery)m_session.createObject(IQuery.OBJECT_TYPE,ItemConstants.CLASS_ITEM_BASE_CLASS);

    // Turn off case-sensitivity
    // Specify the criteria data

query.setCaseSensitive(false);

query.setCriteria("[Title Block.Number] contains (%0)",

new String[] { this.txtItemNum.getText().toString() });

    // Run the query

ITable queryResults = query.execute();

Iterator i = queryResults.iterator();

    // If there are no matching items, display an error message.

if (!i.hasNext()) {

JOptionPane.showMessageDialog(null, "No matching items.", "Error",

JOptionPane.ERROR_MESSAGE);

return;

}

    // Define arrays for the table data

final String[] names = {"Item Number", "Item Description"};

final Object[][] data = new Object[resultCount][names.length];

int j = 0;

while (i.hasNext()) {

IRow row = (IRow)i.next();

data[j][0] =

row.getValue(ItemConstants.ATT_TITLE_BLOCK_NUMBER).toString();

data[j][1] =

row.getValue(ItemConstants.ATT_TITLE_BLOCK_DESCRITPION).toString();

j++;

}

catch (APIException ex) {

System.out.println(ex);

}

    // Create a table model

TableModel newDataModel = new AbstractTableModel() {

// Add code here to implement the table model

};

    // Populate the table with data from the table model
    }

myTable.setModel(newDataModel);