Index Search Selection Criteria Dialog

Use the Index Search Selection Criteria dialog to enter column criteria or SQL statements for selected tables. The criteria that you enter on the Index Search Selection Criteria dialog is applied to all listed Archive Files stored on the selected media type.

To open the Index Search Selection Criteria dialog, right-click the grid in the Archive Directory Maintenance dialog and click one of the options in the Search submenu.

Index Search Selection Criteria dialog with Column Criteria as the active tab

Table

Click the down arrow to select a name from a superset of tables in all listed Archive Files stored on the selected media type. It is possible to have two or more tables with the same name, but different columns. Thus, the names of all columns in tables with the selected name are displayed in the Index Search Selection Criteria dialog.

Combine all table criteria with

Option for combining criteria for multiple tables:

AND
Data in an Archive File must match selection criteria for all tables.
OR
Data in an Archive File must match selection criteria for one table.

You can use column criteria and SQL WHERE clauses for each table, as appropriate.

Note: If you define both column criteria and an SQL WHERE clause for a table, the specifications are logically ANDed.

Column Criteria Tab

Use the Column Criteria tab to define selection criteria for any column in the table. Enter selection criteria for a specific column next to the column name.

Combine all column criteria with

Option for combining criteria for multiple columns in the table:

AND
A row must match selection criteria for all columns.

For example: CUSTNAME > 'M' AND STATE = 'NJ'

OR
A row must match selection criteria for one column.

For example: CUSTNAME > 'M' OR STATE = 'NJ'

Column Name

Name of each column. You can rearrange the order in which the columns are displayed by dragging the grid row number. You cannot modify column names.

Operator

Click the Operator grid cell to select an operator from a list.

Selection Criteria

Enter a value or substitution variable in the Selection Criteria cell. Values for selection criteria must conform to SQL syntax and include appropriate relational or logical operators. To validate selection criteria, click outside the grid row. If the criteria is invalid, a message indicates the problem.

SQL Tab

Use the SQL tab to create an SQL WHERE clause to handle selection criteria that is more complex than can be defined on the Column Criteria tab.

For example, to search for the desired set of data in a table in an Archive File, you may need to use a combination of AND and OR logical operators.

Note: For details on rules that apply to SQL statements used internally by Archive, refer to SQL Grammar for Search and Restore.
Index Search Selection Criteria dialog with SQL as the active tab

SELECT. . . FROM (table) WHERE

Enter the SQL WHERE clause portion of the SELECT Statement.

  • To create an SQL WHERE clause, type directly into the SELECT text box or select from the Columns and Operators lists.
  • To remove an SQL WHERE clause, right-click the SELECT text box and click Remove SQL. You may also overtype with blanks, or use the Delete or Backspace key.
  • To validate an SQL WHERE clause, right-click the SELECT text box and click Validate SQL, or click outside the text box. If the statement is invalid, a message is displayed.

Columns

A list of the columns in the selected table. Select a column name to add it to the SQL WHERE clause before the cursor position.

Operators

A list of valid operators and symbols you can use in the SQL WHERE clause. Select an operator to insert it before the cursor position.

Note: See SQL Grammar for Search and Restore for more information.

Begin the Search

To begin the search, close the Index Search Selection Criteria dialog.

Depending on whether you selected the Only Use Index to Perform Search check box on the Archive tab of Personal Options, the search proceeds as follows:

  • If the Only Use Index to Perform Search check box is cleared, the data in all listed Archive Files is searched for possible matches to the specified criteria.
  • If the Only Use Index to Perform Search check box is selected, Archive Indexes are searched for possible matches to the specified criteria. This can be a more expedient way to find a match for specified criteria, but may not be definitive. If Archive Index information matches the criteria, but is insufficient to establish a full match (for example, data is indexed but the column for which you provide criteria is not), the Status is shown as Indeterminate. Right-click and click Resolve or Resolve All in the shortcut menu to search the complete set of archived data for matches to the criteria.