Selection Criteria Tab

Use the Selection Criteria tab to provide selection criteria for one or more columns in any table in the Access Definition Table List. The criteria is used to select rows to display, archive, or extract.

Note: If you provide selection criteria and an SQL WHERE clause for a table, the specifications are logically ANDed (rows must meet both conditions). If a Point and Shoot list is also used, it is logically ORed with the other criteria.
selection criteria tab defining a value of NJ for the state column

Correlation Name

Abbreviation for the name in the Table box. Enter an easily typed substitute for the fully qualified table name to use in selection criteria or an SQL WHERE clause.

Note: Changes to the Correlation Name apply on the Selection Criteria and SQL tabs.

Variable Delimiter

Character required to identify substitution variables in selection criteria and SQL WHERE clauses. To change the delimiter, click the down arrow.

Note: Optim automatically revises selection criteria and SQL WHERE clauses to reflect the change.

Combine all column criteria with

Option for combining criteria for multiple columns:

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 in the Table Editor or Point and Shoot Editor by dragging the grid row number. You cannot modify column names and you cannot search an SQL Variant column.

Selection Criteria

Selection criteria for any column.

Note: When a relationship is traversed from child to parent, any selection criteria for the parent table are ignored.

Use an appropriate operator and value or substitution variable specification. Selection criteria must conform to SQL syntax and include relational or logical operators. Logical operators and syntax vary among DBMSs. Refer to the appropriate DBMS documentation for information. Lists of commonly used operators, appropriate to the current DBMS, are provided on the SQL tab.

For information on defining substitution variables, see Variables Tab.

Validate selection criteria by clicking outside the grid. If the DBMS returns an error on an SQL Prepare statement, that message is displayed.

Note: To close this dialog, you must correct any errors.

Remove selection criteria by right-clicking the Selection Criteria cell to open a shortcut menu and selecting Clear, Remove or Remove All. You may also overtype with blanks or use the Delete or Backspace key.

Date Criteria: A unique operator allows you to select data on the basis of values in a DATE column. The syntax for this operator is:

BEFORE (nD nW nM nY)

Use the D, W, M, and Y arguments in any combination to indicate the number of days, weeks, months, or years subtracted from the date at runtime. If no arguments are specified, the current date is used. Rows with a date older than the calculated date are extracted or archived. The n multiplier is an integer and can optionally be preceded by + or -.

Note: You can analyze the effect of selection or date criteria and confirm that it works as expected by selecting the Access Definition Editor. If the table for which you are defining criteria is not the Start Table, temporarily specify it as the Start Table and select Edit Point and Shoot List from the Tools menu. Optim™ displays data that corresponds to the selection criteria. If no data is displayed, the syntax for the selection criteria may be incorrect for the DBMS.

Selection Criteria Identified

A selection criteria icon Selection Criteria icon in the Table Specifications column on the Access Definition Editor Table Listindicates that selection criteria are specified for the table.

Examples

The following examples use operators and syntax that may not be valid for all DBMSs. The functions demonstrated in the examples are universal, however.

Example 1: To obtain data for all customers with names that begin with the letter M, from the state of New Jersey, specify:

Column Criteria
CUSTNAME LIKE 'M%'
STATE = 'NJ'

This example uses explicit values for each column. However, you can also use substitution variables.

For example, to obtain data for the same customers using a variable (ALPHA) for the alphabetic range and a variable (ST) for the state, specify:

Column Criteria
CUSTNAME LIKE :ALPHA
STATE = :ST

Select AND to include all customers that satisfy both conditions. Select OR to include all customers that satisfy either condition.

Example 2: To obtain data for all customers with names that begin with the letters M, N, O, P and Q from the states of California, Arizona and New Mexico, specify:

Column Criteria
CUSTNAME BETWEEN 'M' AND 'Q'
STATE IN ('CA','AZ','NM')

As in the previous example, select AND to include all customers that satisfy both conditions. Select OR to include all customers that satisfy either condition.

Note: Values are validated at run time or you can display data in the Point and Shoot Editor to confirm selection criteria. Processing errors may occur if values are not the correct data type or size, or if the specifications do not match the requirements of the selection criteria SQL.