SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
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. For more information about outer queries, see Subqueries on
page S-82.
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.
For more information, see Access Options on page A-1.
[ 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 UPDATE. If this operation occurs, your
process waits or times out although 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.)