Specifies a name for the list that is to be created.
If an existing list name is specified, the existing list is deleted and a new list is created.
- ([authorization-identifier.] table-identifier)
Specifies the authorization identifier of the table's owner and the name of the base table from which the list to be created.
The following tables cannot be specified in table-identifier:
- Shared tables
- Tables with WITHOUT ROLLBACK specification
- View tables
- Foreign tables
- Table aliases
Specifies conditions for determining the rows that are to be retrieved.
If no search conditions are specified, the list will be created from all the rows in the specified table.
The ANDNOT logical operator can be specified in an ASSIGN LIST statement search condition. The ANDNOT logical operator is evaluated in the same priority order as AND.
The following table shows the results of ANDNOT logical operations:
Left side | Right side |
---|
T | F | ? |
---|
T | F | T | T* |
F | F | F | F |
? | F | ? | ? |
- T: TRUE
- F: FALSE
- ?: Indeterminate
- * C1 = V1 ANDNOT V2 = C2 is not equivalent to C1 = V1 AND NOT C2 = V2.
- If predicate-1 = (predicate-2 ANDNOT predicate-3), the set satisfying predicate-1 is the difference between the set satisfying predicate-2 and the set satisfying predicate-3.
- If predicate-1 = (predicate-2 AND NOT predicate-3), the set satisfying predicate-1 is the result of the AND logical operation between predicate-2 and NOT predicate-3.
- Therefore, in the case of AND NOT, the result indicated by the asterisk in the above truth table will be ? (indeterminate).
The following roles apply to search condition statements:
- None of the following items can be specified in a search condition:
- A subquery (table subqueries without an external reference in an IN predicate without a NOT specification can be specified).
- An arithmetic operation, date operation, time operation, concatenation operation, scalar function, CASE expression, or CAST specification
- Comparison of one column against another when written with a comparison operator as follows:
column-specification comparison-operator column-specification
- A comparison predicate whose both sides are a literal, USER, CURRENT_DATE, CURRENT_TIME, or CURRENT_TIMESTAMP[(p)]
- A BETWEEN predicate where value-expression-1 is a column specification and either value-expression-2 or value-expression-3 is a column specification
- A component specification
- A function call (functions provided by a plug-in in which index-using logic is implemented can be specified)
- Logical predicates IS FALSE and IS UNKNOWN
- A table name or a correlation name cannot be specified in column-name in a search condition.
- If a repetition column is specified in a search condition, the subscript ANY must also be specified.
- An index (except for an index having an exception key for a column that is specified to the IS NULL predicate) must be defined for all columns that are specified in a search condition. For a structured repetition predicate, a multicolumn index that contains all repetition columns in its constituent columns, specified in a search condition in the structured repetition predicate, must be defined.
- When evaluating the predicate for the non-repetition column by using an index that contains both a repetition column and a non-repetition column as constituent columns, the index can be used if a condition is specified for one of the repetition columns.
- Either of the following indexes must be defined on a column in an IN predicate with a table subquery specification. Notice that case (b) is limited to the situation in which the SQL optimization mode is optimizing mode 2 based on cost:
(a) Single-column index
(b) Column in the IN predicate with a table subquery specification that is not the first column in a multicolumn index
Even if the column in the IN predicate with a table subquery specification is not the first column in a multicolumn index, a column need not be defined if one of the following is specified in an index constituent column preceding this column: a comparison predicate (=), the NULL predicate (IS NULL), or an IN predicate (IN) for which the right-hand side is a value specification. This rule, however, excludes indexes that contain a repetition column as an index constituent column. For the IN predicate for which a value is specified on the right side, the number of value specifications must satisfy one of the following conditions:
- If IN is in only one column, the number of value specifications is 5 or less
- If IN is in two or more columns, the product of numbers of value specifications specified in the columns is 5 or less.
- If no search conditions are specified, an index (other than a plug-in index or an index that has an exception key) must be defined for one of the columns (other than a repetition column) for each of the tables from which the list is derived.
- A predicate in which a repetition column is specified cannot be negated by the NOT logical operator.
- A predicate that includes the ANDNOT logical operator cannot be negated by the NOT logical operator.
- Logical predicates cannot be negated by NOT.
- The IN predicate for which a table subquery is specified cannot be negated by the NOT Boolean operator.
- [WITHOUT LOCK [{WAIT | NOWAIT}]]
Omission of this operand specifies that once data has been retrieved, it must be guaranteed until completion of the transaction.
- WITHOUT LOCK [WAIT]
- Specifies that once data has been retrieved, it does not have to be guaranteed until completion of the transaction. The WITHOUT LOCK [WAIT] option causes HiRDB to release the lock without waiting for completion of the transaction, thus improving the system's capacity for concurrent execution.
- WITHOUT LOCK NOWAIT
- Specifies immediate lock release when data being updated by another user must be referenced immediately, or the integrity of data after it has been retrieved once need not be maintained before the transaction has been completed.
- When the WITHOUT LOCK NOWAIT option is specified, HiRDB does not perform locking. Because a table that is being updated can be retrieved without waiting for locking, the system's capacity for concurrent execution can be improved. However, retrieving data while it is being updated may produce incorrect results.
Omission of this operand specifies that if the table to be retrieved is being used by another user, this operation is to be placed on hold until the other transaction is completed, and then this operation is to be executed (except when the WITHOUT LOCK NOWAIT option is specified).
- WITH ROLLBACK
- Specifies that this transaction is to be canceled and invalidated if the table to be retrieved is being used by another user.
- NO WAIT
- Specifies that if the table to be retrieved is being used by another user, this transaction is to be flagged as an error but is not to be canceled. However, locking applied during execution of this SQL statement is not released.