Content Engine Query Builder (Query Builder tab)

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.

Select a view and mode

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: Search 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: Search template 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: Search 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: Search template icon.

Query Builder's Simple View

Select from Table
Select the class whose objects you want to search for. For example, the Document table provides columns with attributes that describe documents. Running a search on the Document table under a specific object store enables you to find documents created in the object store.

Click the down arrow to display 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.

Select Columns
Displays the properties of the object selected in the Select from Table field. This enables you to select which properties you want displayed in the results pane when the query completes its search.

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 Creator, Current State, and Date Created columns.

Criteria
Enables you to specify the criteria for selecting objects from the selected table. You can specify up to four conditions by selecting a property from the list next to one of rows A through D. The Condition column provides a list of conditions you can place on the selected properties. Enter the desired value in the Value field.
Criteria Grouping
Enables you to group the four conditions specified in the Criteria fields. By default, the query uses AND, which requires that an object meet all conditions to be included in the query results. Click the button to toggle its function between AND and OR.

Query Builder's SQL View

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 could cause you to lose any changes made in the SQL query view.

Query Timeout Limit (seconds)

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.

Maximum Results Displayed

Enter the maximum number of results. The query will stop when the number of objects in its result set reaches this number.

Description

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.