SQL Tab

Use the SQL tab to create an SQL WHERE clause to handle selection criteria that are more complex than can be defined on the Selection Criteria tab. For example, to select the desired set of data for a table, you may need a combination of AND and OR logical operators.

Note: If you specify an SQL WHERE clause and selection criteria 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.
sql tab where you create sql where clause

Correlation Name

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

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

Variable Delimiter

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

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

SELECT. . . FROM (table) WHERE

Enter the SQL WHERE clause portion of the SELECT Statement. To:

Columns

A list of the columns in the table. Select a column name to add it to the SQL WHERE clause at the cursor position. You cannot search an SQL Variant column.

Operator Symbols

A list of valid operator symbols that you can use in the SQL WHERE clause. Select an operator symbol to insert it at the cursor position.

Operators

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

The BEFORE operator allows you to select data on the basis of date. The syntax for this operator is:

colname BEFORE (nD nW nM nY)

The column referenced by colname must be a DATE column. 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 in colname that is older than the calculated date are extracted or archived. The n multiplier is an integer and can optionally be preceded by + or -.

SQL WHERE Specifications Identified

An SQL icon SQL icon in the Table Specifications column on the Access Definition Editor table List indicates that SQL WHERE clause specifications apply for the table.