SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
S-25
Considerations for UNION
A grouped view is a view defined with a CREATE VIEW AS clause that contains a
GROUP BY or HAVING clause that is not in a subquery, contains an aggregate
function in the select list, or refers to a grouped view in the FROM clause.
A shorthand view whose definition is based on a union of SELECT statements or
that contains a LEFT JOIN operator, cannot participate in another join operation. A
shorthand view whose definition contains any join operation cannot be the inner
table of a LEFT JOIN.

If you include a GROUP BY clause, the columns you refer to, in expressions in the
select list, must be either grouping columns or arguments of a function. There must
be no more than one row in the result table for each group.
For example, if AGE is not a grouping column, you can refer to AGE only in a
function invocation, such as AVG (AGE). If you do not include a GROUP BY
clause, but you specify a function in the select list, all rows of the result table form
a group. AVG and SUM cause a single value for the table, and COUNT counts all
rows. In this case, the select list can contain only functions because there are no
grouping columns.
If you specify a GROUP BY clause, a function applies to each row of each group.
The result of AVG and SUM is a value for each group. COUNT returns, for each
group, the number of rows in that group.

Columns containing multibyte characters cannot be displayed on all types of output
devices.

You can alter the =_SORT_DEFAULTS define to specify a scratch file name, and
certain other options to be used by the SORTPROG process. This technique is
useful if you are selecting data from a large table. For more information, see
=_SORT_DEFAULTS DEFINE on page Z-4 or the FastSort Manual.
Considerations for UNION

The contributing SELECT statements are called SELECT1 and SELECT2.

The contributing tables resulting from the SELECT statements are called TABLE1
and TABLE2.

The table resulting from the UNION operation is called RESULT.
Characteristics of UNION Columns
For columns in the same ordinal position in TABLE1 and TABLE2:

If both columns contain character strings, the corresponding column in RESULT
contains a character string whose length is equal to the greater of the two
contributing columns.

If both columns contain variable-length character strings, RESULT contains a
variable-length character string whose length is equal to the greater of the two
contributing columns.