SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
S-26
ORDER BY clause and UNION operator

If both columns are of exact numeric data types, RESULT contains an exact
numeric value whose precision and scale are equal to the greater of the two
contributing columns.

If both columns are of approximate (floating point) numeric data types, 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, 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, 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, RESULT does not allow null
values; otherwise, RESULT allows null values.
These restrictions apply for shorthand views using UNION:

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.

This example on SELECT illustrates the correct usage of ORDER BY:
SELECT A FROM T1 UNION SELECT B FROM T2 ORDER BY A

This example on SELECT is incorrect, however, because the ORDER BY clause
does not follow the final SELECT:
SELECT A FROM T1 ORDER BY A UNION SELECT B FROM T2