SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-193
Considerations for SELECT
overflows. See the run-time limits on streams in the SQL/MX Queuing and
Publish/Subscribe Services for details.
•
You cannot use streams with nonaudited tables.
•
Stream access within compound statements is not supported.
Joining the Results of an Embedded Delete or Update
SQL/MX Release 2.x enables you to join another table with the results of an embedded
delete or embedded update. For more information, see SQL/MX Queuing and
Publish/Subscribe Services.
Restrictions on Embedded Deletes and Updates
These restrictions apply to embedded deletes and updates:
•
You cannot use an embedded delete or update with a union statement—not even
UNION ALL.
•
When you use an embedded delete or update with a join, the join predicate must
use the other table’s primary key.
•
You cannot join an embedded delete or update with a stream.
•
You cannot join an embedded delete or update with another embedded delete or
update.
•
The table referenced by an embedded delete or update cannot be referenced
again in the same statement.
•
Rowsets cannot be used as the selection predicate for an embedded delete or
update.
•
An embedded deletes or update cannot be used with a compound statement.
•
You cannot use an embedded delete or update with aggregates (for example,
GROUP BY, HAVING, or DISTINCT).
•
You cannot sort an embedded delete or update. Therefore, you cannot use
DISTINCT or ORDER BY unless they are supported by an index. You can use a
secondary index to access an ordered embedded delete or update only if the
columns in the index definition include all the columns of the base table accessed
as an embedded delete or update that are referenced in the WHERE clause.
DISTINCT Aggregate Functions
An aggregate function can accept an argument specified as DISTINCT, which
eliminates duplicate values before the aggregate function is applied. Only one
DISTINCT aggregate function is allowed at each level of a SELECT statement. Multiple
DISTINCT aggregates are allowed if they are on the same column, but are not
Pub/Sub
Pub/Sub