When you use selection criteria or SQL statements to find
specific data in an Archive File, the syntax and grammar may differ
slightly from SQL syntax and grammar you are accustomed to using with
a particular DBMS.
The following rules apply to SQL statements
used internally by Archive:
- No DISTINCT after SELECT
- No arithmetic expressions (only columns (eventually
qualified), literals or parameters)
- No GraphicLiteral
- No TextLiteral
- No functions
- No subselect in WHERE clause
- Only one table in the FROM clause
- No ORDER BY
- No GROUP BY
- No END keyword or operator required (for example,
no semicolon required)
Any level of complexity, using parentheses and
boolean operators, can be expressed in a WHERE clause.
All comparison operators are supported, as are BETWEEN, IS NULL, IN,
and LIKE operators, with their NOT form.
SELECT. . . FROM (table) WHERE
The SQL tab of the Selection
Criteria dialog contains the statement:
SELECT...
FROM dbalias.creatorid.tablename WHERE:
- SELECT...
- Rows are selected from the named table according to the WHERE clause
you create.
- FROM
- The WHERE clause is applied to the table named
after this keyword. Select the table for the SQL statement from the
drop-down list.
- WHERE
- Create a WHERE clause in the text box below the
Select statement, using column names, operators and values to complete
the statement. You can type directly into the text box, and select
column names and operators from the lists. Selected column names and
operators are inserted in the text box at the cursor position.
Note: Dates
must be expressed as string literals using all or part of the format:
'YYYY_MM_DD_HH_MM_SS_FFF[FFF]' where
the separators can be underscore ( _ ), dash ( - ), colon ( : ), front
slash ( / ), period ( . ), or blank space.
- To validate the SQL WHERE clause,
right-click the SELECT text box and select Validate SQL,
or click outside the text box. If the statement is invalid, a message
is displayed.
- To remove the SQL WHERE clause,
right-click the SELECT text box and select Remove SQL.
You may also overtype with blanks, or use the Delete or Backspace
key.