NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
S-21
SELECT Statement
HAVING search-cond
specifies a search condition to apply to each row of the result table of the previous
clause. The search condition is applied to each group, or (if there is no GROUP BY
clause) to all the rows.
In the search condition, you can specify any column as the argument of a function
(for example, AVG (SALARY)). A column that is not in a function must be,
however, a column in a GROUP BY clause or a column in a table or view specified
in an outer query. See Subqueries
on page S-81 for more information about outer
queries.
If the FROM clause specifies a grouped view, you cannot specify a HAVING clause.
[ FOR ] { BROWSE | STABLE | REPEATABLE } ACCESS
specifies the access mode for the SELECT.
The default is FOR STABLE ACCESS, which allows concurrent use of the
database, but limits access to a row while the row is processed.
See Access Options
on page A-1 for more information.
[ IN ] { SHARE | EXCLUSIVE } MODE
specifies that SHARE or EXCLUSIVE locks be used on accessed rows of the table
and of the index, if any, through which the accesses occur.
Use SHARE mode when your process reads data but does not modify it. Specifying
STABLE access and SHARE mode ensures greater concurrency.
Use EXCLUSIVE mode when your process reads data and then modifies it with
DELETE or UPDATE. Requesting EXCLUSIVE locks on the SELECT prevents
other processes from acquiring SHARE locks on the accessed rows between the
time of the SELECT and the time of the subsequent DELETE or UPDATE. Such
locks by other processes would prevent your process from escalating its own
SHARE locks to the EXCLUSIVE locks required for a DELETE or UPDATE
operation, causing your process to wait or timeout.
Note that a SELECT locks only the accessed row in the table and the corresponding
row in the index used as the access path for the SELECT. Another process using an
index-only path with STABLE or REPEATABLE access can lock rows in an index
on the table that was not used as the access path for the SELECT but that is affected
by the DELETE or UDPATE. If this operation occurs, your process will wait or
timeout even though you specified EXCLUSIVE. (You can avoid this scenario by
using the LOCK TABLE IN EXCLUSIVE MODE statement to lock the table and
all its indexes, but this prevents other processes from accessing any portion of the
table while the lock is in effect and might not be the best solution to the problem.)
The IN clause is not allowed for BROWSE ACCESS. IN SHARE MODE is ignored
for cursor SELECT operations.
If you omit the IN clause, SQL uses SHARE until an attempt is made to modify the
data, then escalates the lock to EXCLUSIVE.