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's features.
You can run Query Builder in either Simple or SQL views. Each view can produce a simple search or a search template that prompts you for search parameters.
View and Mode | Available features |
---|---|
Simple View > Search Mode On the View menu, click Simple View. Then on the View menu again, click Search Mode. |
Use Query Builder to select the class, columns, criteria, criteria grouping, timeout limits, maximum results, and description. Any actions you specify in the Actions tab, Script tab, or Security tab will also be carried out as part of the query. On the File menu, click Save
or Save As to save the search with a *.sch
file. Enterprise Manager displays the saved search with this icon: Click OK to run the query. |
Simple View > Template Designer Mode On the View menu, click Simple View. Then on the View menu again, click Template Designer Mode. |
Like Search mode, except you choose which criteria to prompt for when the template is launched, by selecting criteria from View column. Enterprise Manager displays the saved search template with this icon: |
SQL View > Search Mode On the View menu, click SQL View. Then on the View menu again, click Search Mode. |
Use the Query Builder's SQL text window to directly enter SQL commands. By default, the SQL Text window will display the SQL equivalent of any search parameters already entered into the Simple View of the Query Builder. Enterprise Manager displays the saved search with this icon: |
SQL View > Template Designer Mode On the View menu, click SQL View. Then on the View menu again, click Template Designer Mode. |
Like SQL View > Search Mode, except the SQL code includes TEMPLATE_VALUE parameters. Enterprise Manager displays the saved search template with this icon: |
Click the down arrow to drop-down the list of all object types available in the database. Select the object table you want to search. The query builder displays all columns defined in the selected table in the Select Columns window.
By default, the query builder selects the asterisk (*), which will display all properties in the search result.
To reduce the number of columns you want displayed, select just the properties you want included in the query result by clicking each property.
For example, if you chose the Document table
and want to display only the creator, current state, and creation date
in the displayed search results, you would choose the objects shown in the
following example:
The SQL view displays the SQL text representation of the Query designed through the Simple View. If you're experienced 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 Query view, the SQL Text window displays the SQL commands for the selections made in the Simple view.
The following shows an example of the SQL commands generated from a Simple Query 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 unfile 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 make changes in SQL query view, the query builder does not attempt to create a simple query view to represent those changes. Moving from SQL view to simple view coulds cause you to lose any changes made in the SQL query view.
Time: Enter the number of seconds after which the query will timeout. This timeout value does not include time required to render the results in the Search Results window. Rendering results will sometimes take longer than executing the search itself.
Maximum: Use the object store's Query tab (right click the object store node, select Properties and go to the Query tab) to enter the maximum query time limit. This value will then appear as a read-only value in the Query Builder.
Enter the maximum number of results. The query will stop when the number of objects in its result set reaches this number.
If you intend to save the search or search template, you can optionally include a description that will help you identify the search when it displays in Enterprise Manager's Saved Searches node.