Information Query

This chapter describes how to use queries to locate information in your application. It includes the following topics:

About Queries

You use queries to locate one or more records that meet specified criteria. In contrast, you use a search operation (see About Search Functionality) to perform a broad search that does not require you to specify specific fields to search on.

You can view the records that a query finds on-screen, export them to a file, or use them as input for a report. A query searches the database for specific data by using conditions or criteria. For example, you might want to find all open service requests with a high priority. You perform this query in the Service screen by using the query criteria of a Status field value of Open and a Priority field value of High.

After you create a query, you can save it, and run it again later.

Note: Drilldown in a query is not supported. In a query, results are returned in a field which is editable and this does not allow drilldown.

Accessing the Query Control

You access the query control by clicking New Query on the application taskbar. Within the query control, you access various drop-down lists for record fields, which helps you narrow your query criteria.

About the Saved Queries Drop-Down List

You can select saved queries from the Saved Queries drop-down list on the application toolbar. In this list, you can access your saved queries and the predefined queries established by your organization.

Predefined Queries

Your organization can provide predefined queries (PDQs). Predefined queries have established criteria, and appear in the drop-down list for the Saved Queries field. You cannot change predefined queries.

For example, if you always work with accounts located in California, then the drop-down list for the Saved Queries field might contain a PDQ called CA Accounts that finds all the accounts in California. Each time you want to see only the California accounts, you do not have to create a query. Instead, you can select the predefined CA Accounts query from the drop-down list for the Saved Queries field.

When you execute a predefined query, the query runs against all the data for the current screen. If you run a query to narrow the data set, then the predefined query uses all data for the screen, not just the data that appears in a list.

To execute a predefined query

Do one of the following:
- Click Execute Query on the application taskbar to execute a predefined query.
- Use the appropriate keyboard shortcut (ALT+R).

Case Insensitive and Accent Insensitive Queries

Query features provide indexes that directly support case insensitive and accent insensitive (CIAI) queries on eligible text columns. CIAI queries ignore both the case and use of accents to return all records that otherwise match the query criteria. This capability is important when searching for records such as contact or customer names. For example, an exact-match query produces different a query results than a CIAI query if the query results contain capitalization inconsistencies (such as MacArthur and Macarthur) or accent usage inconsistencies.

CIAI queries apply to the entire query string. To find out if your application uses exact-match queries or CIAI queries, run test queries and review the query results. For more information about CIAI queries, see Siebel Global Deployment Guide.

Your Siebel administrator can configure specific columns for CIAI queries by defining CIAI columns and CIAI indexes in the repository using the CIAI wizard in Siebel Tools. The CIAI wizard sets the Default Insensitivity property for CIAI columns to DB Case & Accent. CIAI-enabled fields are blank in the user interface. The fields that contain the Case Required property represent non CIAI-enabled fields.

A case-insensitive query works on all supported databases for the current release of the application. However, an accent-insensitive query works only on the databases that provide the accent-insensitivity feature, such as Microsoft Structured Query Language (SQL). For more information about supported databases, see Siebel System Requirements and Supported Platforms on Oracle Technology Network.

Note: For Siebel CRM product releases 8.1.1.9 and later and for 8.2.2.2 and later, the system requirements and supported platform certifications are available from the Certification tab on My Oracle Support. For information about the Certification application, see article 1492194.1 (Article ID) on My Oracle Support.

Queries Toolbar

When performing a query, a queries toolbar appears on lists and forms. The following table describes the typical buttons that appear on the queries toolbar. Your application might contain additional buttons or other elements on each list or form toolbar.

Table Common Buttons in Queries

Button	Button Name	Description
	Query Assistant	Click Query Assistant to start the query assistant. For more information about the query assistant, see Using the Query Assistant.
	Go	Click Go to execute the query.
	Cancel	The Cancel to cancel the query.

Creating Queries for Records

You can create your own queries, execute them, and save them for later use. Queries that you create are called user-defined queries. In these queries, you can enter your own criteria to locate a specific set of records.

To create a query for records

Navigate to the screen.
Do one of the following:
- In the list or form, click Query (the magnifying glass icon).
- In the list or form, click Menu (the cogwheel icon), and then click New Query.
- From the application-level menu, choose Query, then New Query.
- From the application taskbar, click the New Query button.
- Use the appropriate keyboard shortcut.
When you invoke the new query command, a blank form or a blank row in a list appears.
Enter the query criteria in the appropriate fields.
A wildcard is automatically assumed after text you enter in the query fields. If you search for Siebe, then all words beginning with the letters Siebe are found.
If you query a data field that has a calendar select button to display the calendar control, then you must include the date field value in quotes. For more information about entering query criteria, see Simple Query Operators.
Do one of the following:
- In the list or form, click Go.
- In the list or form, click Menu (the cogwheel icon), and then click Run Query.
- From the application-level menu, choose Query, and then Run Query.
- From the application taskbar, click the Execute Query button.
- Use the appropriate keyboard shortcut.
The query executes, and the records that match the query criteria appear.
To save the query, then do the following:
1. From the application-level menu, choose Query, then Save Query As.
  The Save Query As dialog box appears.
2. In the Query Name field, enter a name for the query, and click OK.
  The saved query appears in the drop-down list for the Saved Queries field.

Finding Records in Lists

A list can contain many records. You might find it necessary to search for the records you want to see in the list. You can perform full- or partial-text searches on one field.

Finding records is similar to querying for records, but you do not save search results when you find records.

To find records in a list

Navigate to the screen.
In the list header, select a field you want to search from the drop-down list for record fields.
If you do not select a field to filter your search against, then all fields will be searched.
In the list header, type the text you want to search for in the field for a search value.
A wildcard is automatically assumed after text you enter in the value field. If you search for Siebe, then all words beginning with the letters Siebe are found.
Click Go.
The records that match the criteria appear.

Refining Queries

You can refine the criteria of a predefined query or a user-defined query.

Note: If you refine the criteria of a predefined query, then you must save the query with a different name to save the query.

To refine a saved query

Navigate to the screen.
From the drop-down list for the Saved Queries field, select the query.
The records meeting the query criteria appear.
Do one of the following:
- In a list or form, click Menu (the cogwheel icon), and then click Refine Query.
- From the application-level menu, choose Query, then Refine Query.
- Use the appropriate keyboard shortcut.
Add and edit the criteria.
Do one of the following:
- In the list or form, click Menu (the cogwheel icon), and then click Run Query.
- From the application-level menu, choose Query, then Run Query.
- Use the appropriate keyboard shortcut.
The query executes, and the records that match the criteria appear.

Deleting Queries

You can delete user-defined queries.

To delete a query

Navigate to the screen.
From the application-level menu, choose Query, then Delete Saved Query.
The Delete Record dialog box appears, showing the user-defined queries for that screen.
Select the query you want to delete in the Query Name list.
Click OK to delete the query.

Canceling Long-Running Queries

Because of how your application is set up, you might be able to cancel queries that run for a long time. For more information about your setup, contact your administrator.

To cancel a long-running query

Execute a query.
If the query does not return records after a specified period, then a dialog box appears.
Click Cancel.
A confirmation dialog box appears to confirm that the query canceled successfully.
Click OK.

Using the Query Assistant

After clicking Query (the magnifying glass icon) in a form or list, you can click the Query Assistant button to create a query. When you use the Query Assistant, you can select operators, instead of entering them, to find the information you are looking for. You can also save your query before you execute it by clicking Save Query.

The following image shows an example of the Query Assistant dialog box. In this example, the Query Assistant queries for all accounts where:

Account equals Active Systems – Head Quarters
AND
First Name field starts with P

Querying in the Query Assistant is case-insensitive.

Example of the Query Assistant Dialog Box

To perform a query using the Query Assistant

Navigate to any screen.
In the list or form, click Query (the magnifying glass icon).
When you invoke the new query command, a blank form or a blank row in a list appears.
Click Query Assistant (the portrait icon).
The Query Assistant dialog box appears.

Note: If no values appear in the drop-down lists in this dialog box, then contact your administrator.
In the Query Assistant dialog box, complete the first row as follows:
1. From the <Choose Field> drop-down list, select a field to query.
2. From the Starts With drop-down list, select an operator for the selected field.
3. Enter the value applicable to the selected operator in the text box.
  Complete the second, third, and other rows as needed in the same way.
4. Select AND or OR from the Perform Query Using drop-down list to specify the type of operator you want to use between each row of your criteria.
  AND shows only results that meet all the criteria.
  OR shows results that meet any of the criteria.
5. Click Go.
  The query executes, the Query Assistant dialog box closes, and the records that match the criteria appear.

About Using Default Queries

When you navigate to a screen, the records that appear depend on the default query set up for that screen. You can specify a different default query in your user preferences. If no default query is set up, then the first predefined query in the drop-down list for the Saved Queries field executes. For more information, see Default Queries.

Note: You cannot set up a blank query as the default. Also, you cannot set your user preferences so that no query executes when you navigate to a screen.

If you set up a default query for the view associated with a visibility filter, then the query executes only if you access that view using the Site Map link. Using the visibility filter to access that filter’s default query fails. If the visibility filter is set up as the default view for that screen, then navigating to the screen executes the default query for the filter.

About Querying a Telephone Number

When you perform a query for a telephone number, the country code of the default country in your computer’s regional settings is automatically added as a prefix to the query value. (However, if the default country is the United States, then no prefix is added.) For example, if the default country is Germany, and if you type 0181 in the telephone field of a query, then the German country code (+49) is added to the query value, and the query value is +49*0181*.

To find a telephone number for a country other than the default country, you must specify the country code in the query value. To avoid limiting the query to one country, you must refine the query to remove the country code portion.

For example, a European user enters the phone numbers of business contacts throughout the continent. When traveling to another country, the user sets the default country on a laptop computer to that country. When in Germany, the user needs local phone numbers. Therefore, German numbers are returned by default for each query. If the user wants to find the number of a contact in England while in Germany, then the user must enter +44 before the query. When next traveling to England, the user does not use German contacts as much. Therefore, only English numbers are returned by default for each query.

Note: Including a hyphen in a telephone number query causes the query to fail. Do not include hyphens in telephone number queries.

Simple Query Operators

You can use several simple query operators to define query criteria.

Note: Query operators are reserved in Siebel query language. If you enter a query value that includes a query operator, then you must enclose the query value in double quotes. For example, a query for records containing the text call is closed fails. The word IS falls into the category of reserved Siebel query language because it is used in the query operators IS NULL, IS NOT NULL, and so on. If you enclose the text call is closed in double quotes (“call is closed”), then the Siebel application returns all records containing the text call is closed. Other reserved operators include apostrophe (’), brackets ( [ ] ), comma (,), parentheses (()), tilde (~), and comparison operators such as =, <, and >.

The following table describes simple query operators. In this table, operators appear in uppercase. However, query strings are case-sensitive. The operators do not have to be in uppercase.

You can use simple query operators on their own.

Table Simple Query Operators

Operator	Description	Example
* (asterisk)	Wildcard operator. Placed anywhere in a string, returns records containing the string or containing the string plus any additional characters, including a space, that appear at the location of the asterisk. You cannot use an asterisk to find dates. To find words on more than one line in a field, you must use an asterisk to separate the words. You cannot query for control characters or non-printable characters, such as line feeds (LF) or carriage returns (CR).	`rang` finds the following: arrange, arranged, orange, orangutan, range, ranges, ranging, rang, strange, stranger, strangest, strangle, wrangle, and so on. If performance is poor when you use the asterisk, then substitute "IS NOT NULL" in the query field. This substitution often improves performance.
? (question mark)	Wildcard operator. Placed anywhere in a string, returns records containing the characters in the string plus any one additional character that appears at the location of the question mark.	`?rag` finds the following: brag, crag, or drag. `t?pe` finds type and tape, but not tripe.
"" (double quotation marks)	Placed before and after a string, returns records that exactly match the string, unless modified by a wildcard operator (* or ?). Quotes find a group of words in the exact order with the exact uppercase or lowercase lettering.	`"Oracle Solaris"` finds records that contain Oracle Solaris in the query field.
= (equals)	Placed before a value, returns records containing a value equal to the query value.	`=Smith` finds all records for which the value in the query field is Smith. It also negates wildcard operators within the query value. For CIAI-enabled fields, if you type `"=abc*"`, then the query becomes a case-sensitive query because you use an equal sign (=) as an operator in the query.
< (less than)	Placed before a value, returns records containing a value less than the query value.	`<6/20/01` finds all records in which the value of the query field is earlier than 20 June 2001. When entering a date, use the format that is specific to your implementation.
> (greater than)	Placed before a value, returns records containing a value greater than the query value.	`>5/31/01` finds all records in which the date in the query field is later than 31 May 2001. When entering a date, use the format that is specific to your implementation.
<> (not equal to: less than and greater than symbols)	Placed before the value, returns records containing a value that is not equal to the query value.	`<>6/20/01` finds all records in which the date in the query field is not 20 June 2001. `<>Paris` finds all the records in which the value in the query field is not Paris.
<= (less than or equal to)	Placed before a value, returns records containing a value less than or equal to the query value.	`<=500` finds all the records in which the value in the query field is less than or equal to 500.
>= (greater than or equal to)	Placed before a value, returns records containing a value greater than or equal to the query value.	`>=500` finds all records in which the value in the query field is greater than or equal to 500.
NOT LIKE, not like	Placed before a value, returns records not containing the value.	`NOT LIKE Smi*` finds all records in which the value in the query field does not start with Smi.
IS NULL, is null	Placed in the query field, returns records for which the query field is blank.	`IS NULL` in the Due Date query field finds all records for which the Due Date field is blank.
IS NOT NULL, is not null	Placed in the query field, returns records for which the query field is not blank.	`IS NOT NULL` in the Due Date query field finds all records for which the Due Date field is not blank.
~ (tilde)	Placed before LIKE and a value with a wildcard operator, returns all matching records regardless of case.	`~LIKE Smi` finds all records in which the value in the query field starts with Smi, smi, SMI, and so on. Using this operator might affect performance. You can enter a CIAI query expression for a contact as follows: `[Last Name] ~Like abc`

Compound Query Operators

The following table shows compound query operators. In this table, operators appear in uppercase. However, query strings are case-sensitive. The operators do not have to be in uppercase.

When you perform a compound query, you must use parentheses to control the order in which the search for matching records is performed.

Table Compound Query Operators

Operator	Description	Example
AND, and	Placed between values, returns only records for which all the conditions are true.	`performance AND memory` finds all records that contain both performance and memory in the query field.
OR, or	Placed between values, returns records for which at least one condition is true.	`performance OR memory` finds all records that contain either performance or memory in the query field. `performance* OR memory*` finds all records that start with either performance or memory in the query field.
NOT, not	Placed before a value, returns only records that do not contain the value.	`performance AND NOT LIKE memory` finds all records that contain performance but not memory in the query field. `NOT (performance OR memory)` finds all records that contain neither performance nor memory in the query field.
() (opening and closing brackets)	Placed before and after the values and operators that are processed first, regardless of the default processing order.	`(sun OR moon) AND NOT stars` returns records that contain sun or moon, but not stars, in the query field.
LIKE, like	Placed before a value, returns records containing the value.	`(performance* OR memory*) AND LIKE (problem)` finds all records in which the query field starts with performance or memory and also includes problem. The LIKE operator is case sensitive. To find matches regardless of case, see Simple Query Operators.

How Siebel CRM Handles Wildcards

To include a wildcard in a search string (for example, "ABC*" or "*ABC?"), you can explicitly include it. Siebel CRM can also append a trailing wildcard (*) if all of the following conditions are true:

The search string does not contain any wildcard. For example: "ABC".
The = (equal) sign does not precede the search string.
The AutomaticTrailingWildcards parameter in the InfraUIFramework section of the application CFG file is set to TRUE or does not exist. The uagent.cfg file is an example of an application CFG file. If the parameter is:
- TRUE. Siebel CRM automatically creates a trailing wildcard or adds a LIKE predicate. If you use a script, and if this script creates a query, then Siebel CRM adds a LIKE statement to the SQL that this script creates.
- FALSE or does not exist. Siebel CRM does not automatically create a trailing wildcard or add a LIKE predicate. It typically sets this parameter to FALSE to avoid using the Use Literals For Like user property.

Wildcards are handled differently when you perform a wildcard search in a popup window using the filter search fields and using the Query button as follows:

Filter search fields. In this case, searching for *Ave returns all records containing *Ave, for example, as follows:
Eight Ave Gourmet Market Fourth Ave. Grocery Gourmet Market 5th Ave
```
Eight Ave Gourmet Market 
Fourth Ave. Grocery 
Gourmet Market 5th Ave
```
In this instance, a trailing wildcard (*) is appended to the search string.
Query button. In this case, searching for *Ave returns only records ending with *Ave as follows:
```
Gourmet Market 5th Ave
```
In this instance, a trailing wildcard (*) is not appended to the search string.

Tips for Queries

Note the following tips when you create and execute queries:

The application automatically adds a wildcard to the end of your query. If you search for Siebe, then all words beginning with the letters Siebe are found. For more information, see How Siebel CRM Handles Wildcards.
When you create a query, the records matching the query criteria appear in a list. While you work with the records in the list and move through the views within the current screen, the list continues to show the records that were found when you executed the query. The list is reset to the default list of records when you leave and then again access the current screen.
If you no longer need the list of records in a query and want to reset the list, then click navigate to the current screen again to return to the default set of records.
If a predefined query created by your organization appears in the drop-down list for the Saved Queries field, then you cannot delete this query from the list. However, you can modify the query criteria and save the query using another name. For more information, see Refining Queries.
You cannot query on Notes views when you are connected to the server.
Any queries that execute in Administration screens against multi-value group fields are automatically appended by EXISTS(). Consequently, you can see every record, regardless of whether it is a primary record.
When you create a query and navigate through the views, use the hyperlinks on the link bar to return to the queried list of records.
If you execute a query that includes more than one consecutive blank space, and if you do not place quotes around the query, then the spaces are ignored, and you see inaccurate results.
When the query results appear in list format, the first record is highlighted. It might appear as though the record you select before running the query appears again, but you are actually seeing a new set of records resulting from your query.
In query mode, check boxes have three states: checked, unchecked, and intermediate. If you specify intermediate in a check box field when running a query, then your application returns records that are checked and records that are unchecked.

Note: The visual representation of the intermediate state of the check box is browser dependent.