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

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-192
Considerations for SELECT
Join Limits
Object Names in SELECT
You can use fully qualified Guardian names only in the FROM clause of a SELECT
statement.
AS and ORDER BY Conflicts
When you use the AS verb to rename a column in a SELECT statement, and the
ORDER BY clause uses the original column name, the query will fail. If a column has
been aliased to another name you must use the alias name. This type of query is not
supported by the ANSI standard.
Stream Access Restrictions
•
SELECT statements can access only one table with stream access except for
unions that allow both SELECT statements to use stream access. However, you
must use UNION ALL when using stream access with unions.
•
Streams assume parallel access to data; that is, if a table is partitioned and you
attempt to access it as a stream, parallel access to partitions is required. If you try
to access a stream when the default ATTEMPT_ASYNCHRONOUS_ACCESS is
set to OFF, NonStop SQL/MX returns an error. See
ATTEMPT_ASYNCHRONOUS_ ACCESS on page 10-54
•
You cannot join two streams.
•
Aggregate functions are not supported on streams, and therefore no GROUP BY
or HAVING clauses are valid on streams.
•
Sort operations are not supported on streams. Therefore, you cannot use
DISTINCT, UNION DISTINCT, or ORDER BY unless supported by an index. You
can use a secondary index for accessing an ordered stream only if the columns in
the index definition include all the columns of the base table accessed as a stream
that are referenced in its WHERE clause.
•
A query expression that serves as a data source for an INSERT statement cannot
specify stream access.
•
A delete or update statement that is not embedded as a table reference cannot
specify stream access. For example, the statement DELETE FROM
STREAM(tab1) is not valid.
•
If your application must handle a fast rate of publishing into the stream, or
publishes rows in very large transactions, it must be able to handle stream
Note. HP recommends that you limit the number of tables in a join to a maximum of 64, which
includes base tables of views referenced in joins. Queries with joins that involve a larger
number of tables are not guaranteed to compile.
Pub/Sub