SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-63
ORDER BY Clause With UNION Operator
To specify a UNION operation, these rules apply:
•
Both select lists must specify the same number of columns.
•
Columns in corresponding positions must have compatible data types. (For
information on compatible data types, see the SQL/MP Reference Manual.)
You can specify a UNION operator in these instances where a single SELECT
statement is allowed:
•
A shorthand view definition
•
A subquery
•
An INSERT operation through a query
•
A nonupdatable cursor statement
Additional guidelines for using the UNION operator follow. In this discussion, catalog
tables are used to demonstrate the UNION operator. Catalog tables are candidates for
a UNION operation because different instances of the catalog tables in different
catalogs have the same attributes for their columns.
To get a list of all programs that are described in the application catalogs from $VOL1
and $VOL2 on your system, you can specify this query:
SELECT A.PROGRAMNAME
FROM $VOL1.PROGCAT.PROGRAMS A
UNION
SELECT B.PROGRAMNAME
FROM $VOL2.PROGCAT.PROGRAMS B ;
For complete information about UNION and UNION ALL, see the SQL/MP Reference
Manual.
ORDER BY Clause With UNION Operator
You can use the ORDER BY clause to order the result of a UNION operation. These
restrictions apply, however:
•
The ORDER BY clause must follow the last SELECT statement and any options
associated with that individual SELECT statement. You cannot use parentheses to
associate an ORDER BY clause with either SELECT statement.
•
The ORDER BY clause must contain one of these:
°
The names of the columns explicitly referenced outside a function or an
expression in the select list
Note. A shorthand view whose definition involves a UNION cannot participate in a join
operation. A SELECT on the view cannot specify a GROUP BY clause, a HAVING clause, or
aggregate functions on any view column. A shorthand view cannot be updated, regardless of
whether the UNION operator is used.