When you use union statements:
All select lists in the union statement must have the same number of expressions (such as column names, arithmetic expressions, and aggregate functions). The following statement is invalid because the first select list is longer than the second:
create table stores_east (stor_id char(4) not null, stor_name varchar(40) null, stor_address varchar(40) null, city varchar(20) null, state char(2) null, country varchar(12) null, postalcode char(10) null, payterms varchar(12) null) select stor_id, city, state from stores union select stor_id, city from stores_east drop table stores_east
Corresponding columns in all tables, or any subset of columns used in individual queries, must be of the same datatype, or an implicit data conversion must be possible between the two datatypes, or an explicit conversion must be supplied. For example, a union between a column of the char datatype and one of the int datatype is possible, unless an explicit conversion is supplied. However, a union between a column of the money datatype and one of the int datatype is possible. See union in the Reference Manual: Commands and Chapter 1, “System and User-Defined Datatypes,” in the Reference Manual: Building Blocks.
You must place corresponding columns in the individual queries of a union statement in the same order, because union compares the columns one to one in the order given in the query. For example, suppose you have the following tables:
Figure 3-2: Union comparing columns
Figure 3-2 shows two tables, T3 and T4. T3 has two columns, “a,” int, and “b,”char(4). T4 contains two columns, “a” char(4), and “b,” int. Each table has three rows: Row 1 shows “1” in the “a” column, and “abc” in the “b” column. Row 2 shows “2” in the “a” column, and “def” in the “b” column. Row 3 shows “3” in the “a” column, and “ghi” in the “b char” column. Table T4, Row 1, shows “abc” in the “a” column, “1” in the “b” column; Row 2 shows “def” in the “a” column, “2” in the “b” column; Row 3 shows “ghi” in the “a” column and “3” in the “b(int)” column.
Enter this query:
select a, b from T3 union select b, a from T4
The query produces:
a b --------- --- 1 abc 2 def 3 ghi (3 rows affected)
The following query, however, results in an error message, because the datatypes of corresponding columns are incompatible:
select a, b from T3 union select a, b from T4 drop table T3 drop table T4
When you combine different (but compatible) datatypes such as float and int in a union statement, Adaptive Server converts them to the datatype with the most precision.
Adaptive Server takes the column names in the table resulting from a union from the first individual query in the union statement. Therefore, to define a new column heading for the result set, do so in the first query. In addition, to refer to a column in the result set by a new name, for example, in an order by statement, refer to it in that way in the first select statement.
The following query is correct:
select Cities = city from stores union select city from authors order by Cities
