NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
S-25
Considerations for UNION
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 will 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 result in 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.
•
Multibyte character sets
Columns containing multibyte characters cannot be displayed on all types of output
devices.
•
Control of the SORTPROG process for large tables
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. See =_SORT_DEFAULTS DEFINE
on
page Z-3 or the FastSort Manual for more information.
Considerations for UNION
Within the following discussion of the UNION operation:
•
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, then RESULT contains a
variable-length character string whose length is equal to the greater of the two
contributing columns.
•
If both columns are of exact numeric data types, then RESULT contains an exact
numeric value whose precision and scale are equal to the greater of the two
contributing columns.