NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
S-26
ORDER BY clause and UNION operator
•
If both columns are of approximate (floating point) numeric data types, then
RESULT contains an approximate numeric value whose precision is equal to the
greater of the two contributing columns.
•
If both columns are of date-time data types, then RESULT contains a DATETIME
value whose precision is the most significant start field to the least significant end
field from the ranges of DATETIME fields in the contributing columns. For
example, if the column in TABLE1 shows YEAR TO DAY and the column in
TABLE2 shows MONTH TO MINUTE, the precision of the corresponding column
in RESULT is YEAR TO MINUTE.
•
If both columns are of INTERVAL data type, then RESULT contains an INTERVAL
value whose range of fields is the most significant start field to the least significant
end field from the ranges of INTERVAL fields in the contributing columns. The
range of INTERVAL fields in RESULT must not contain both year-month and day-
time fields. (The year-month fields are YEAR and MONTH. The day-time fields are
DAY, HOUR, MINUTE, SECOND, and FRACTION.)
•
If both columns are described with NOT NULL, then RESULT does not allow null
values; otherwise, RESULT allows null values.
For shorthand views using UNION, the following restrictions apply:
•
The view cannot participate in a join operation.
•
A SELECT operation on the view cannot specify a GROUP BY or HAVING clause.
•
A SELECT operation on the view cannot specify aggregate functions on any view
column.
ORDER BY clause and UNION operator
In a query containing a UNION operator, the ORDER BY clause defines an ordering on
the result of the union. A SELECT statement cannot have an individual ORDER BY
clause.
You can specify an ORDER BY clause only as the last clause following the final
SELECT statement (SELECT2 in this example).The ORDER BY clause in RESULT
specifies the ordinal position of the sort column either by using an integer or by using
the column name from the select list of SELECT1.
For example, the following SELECT illustrates correct use of ORDER BY:
SELECT A FROM T1 UNION SELECT B FROM T2 ORDER BY A
The following SELECT is incorrect, however, because the ORDER BYclause does not
follow the final SELECT:
SELECT A FROM T1 ORDER BY A UNION SELECT B FROM T2
The following SELECT is also incorrect:
SELECT A FROM T1 UNION (SELECT B FROM T2 ORDER BY A)
Because the subquery (SELECT B FROM T2...) is processed first, the ORDER BY
clause does not follow the final SELECT.