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 appropriate set of data for a table, you might 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.

Example of the SQL tab that is used to create an 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 that is 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 create an SQL WHERE clause, type directly into the SELECT text box or select from the Columns, Relational Operators, and Logical Operators lists.
To remove an SQL WHERE clause, right-click the SELECT text box and select Remove SQL. You also can type over with blanks, or use the Delete or Backspace key.
To validate the syntax of an SQL WHERE clause, right-click the SELECT text box and select Validate SQL, or click outside the text box. If the DBMS returns an error on an SQL Prepare statement, a message is displayed.

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.

Operators (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.

Use the BEFORE operator to select data based on date. The syntax for this operator is as follows:


colname BEFORE (nD nW nM nY)

The column that is 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 that are subtracted from the date at run time. 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 in the Table Specifications column on the Access Definition Editor table list indicates that SQL WHERE clause specifications apply for the table.