SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-64
GROUP BY and HAVING Clauses With UNION
Operator
°
An integer that indicates the ordinal position of a column, a function, or an
expression in the select list
This statement shows incorrect use of the ORDER BY clause and UNION operator:
SELECT A.PROGRAMNAME
FROM $VOL1.PROGCAT.PROGRAMS A
ORDER BY A.PROGRAMNAME
UNION
SELECT B.PROGRAMNAME
FROM $VOL2.PROGCAT.PROGRAMS B ;
Instead, you should formulate the preceding query as:
SELECT A.PROGRAMNAME
FROM $VOL1.PROGCAT.PROGRAMS A
UNION
SELECT B.PROGRAMNAME
FROM $VOL2.PROGCAT.PROGRAMS B
ORDER BY A.PROGRAMNAME ;
GROUP BY and HAVING Clauses With UNION Operator
You cannot use the GROUP BY and HAVING clauses to form groups in the result of a
UNION operation.
Unlike the ORDER BY clause, which can define an ordering on the result of the UNION
operation, the GROUP BY and HAVING clauses are associated only with the SELECT
statement in which the clauses appear. Consequently, the groups are visible only in the
result table of the SELECT statement that contains the clauses and not in the result of
the UNION operation.
Using Collations With the UNION Operator
The UNION operator is affected by collations in associated tables as follows:
•
Comparisons might occur during the computation of the result
•
Because the output of the UNION operation is a table, the default collation for its
columns must be specified
For example, if column A in table X has collation FINNISH and column B in table Y has
collation ITALIAN and you attempt to SELECT A FROM X UNION Y, then column A
has no collation. Instead, you could use this statement:
SELECT A COLLATE FRENCH FROM X
UNION
SELECT A COLLATE FRENCH FROM Y;
Note. When requesting UNION operations, the choice of access option can affect
performance. For more information, see Specifying Access Option and Lock Characteristics
on
page 4-16.