Use the query builder to create, save, and launch queries for objects and optionally to perform bulk operations on them. See Concepts: search and bulk operations for information about the query builder features.
You can run the query builder in either simple or SQL view. Each view can
produce a predefined search or a search template that prompts you for search parameters. Enterprise Manager displays predefined searches with this icon: and search templates with this icon:
.
In the simple view, you can select the class, columns, criteria, criteria grouping, timeout limits, maximum results, and description you want to use for a predefined search. You can specify actions in the Actions tab, Script tab, and Security tab are also carried out as part of the query.
Select the object table you want to search. The query builder displays all columns defined in the selected table in the Select Columns window.
Use the SQL text window to directly enter SQL commands. By default, the SQL text window displays the SQL equivalent of any search parameters already entered in the simple view. If you are experienced in using SQL query commands, you can type in complex queries through this view.
The SQL text window displays the SQL query commands. When you first open this view, the SQL text window displays the most recently used SQL query commands. If you opened the SQL view after specifying search criteria in the simple view, the SQL text window displays the SQL commands for the selections made in the simple view. In search templates, the SQL code includes TEMPLATE_VALUE parameters for defining the criteria to prompt for when the search template is opened.
The following example shows SQL commands generated from a query created in the simple view:
SELECT [This], [Creator], [DateCreated], [CurrentState] FROM [Document] WHERE ([DateCreated] > 20010401 T000000Z AND [Creator] = 'Administrator')
All queries must begin with SELECT This, followed by the SQL commands for column definitions and search criteria.
The following example shows the SQL commands for a document search. This sample requests the report to include columns with values for the document size, creator, and ID. The search criteria specify finding documents with more than zero content or documents created by a user who logged on as Administrator.
SELECT This, ContentSize, Creator, Id FROM Document WHERE (ContentSize > 0 OR Creator = 'Administrator')
The following example shows the SQL commands for retrieving unfiled custom objects:
SELECT CustomObject.This, CustomObject.Name FROM CustomObject LEFT JOIN ReferentialContainmentRelationship rcr ON CustomObject.This=rcr.Head WHERE rcr.Head IS NULL
If you change the query in SQL view, the query builder does not attempt to create a query in the simple view to represent those changes. Moving from SQL view to simple view can cause you to lose any changes made in the SQL view.
Time: Enter the number of seconds after which the query times out. The timeout value does not include the time that is required to render the results in the Search Results window. Rendering results sometimes takes longer than executing the search itself.
Maximum: This value is displayed as a read-only value in the query builder. To change the maximum value, right-click the associated object store node and click Properties. On the Query tab, enter the maximum query time limit.
Enter the maximum number of results. The query stops when the number of objects in its result set reaches this number.
Optional: Enter a description to help you identify the search. The description is displayed in the Enterprise Manager Saved Searches folder.