Guidelines for union Queries

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, “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:

The above table 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, SAP ASE converts them to the datatype with the most precision.

SAP ASE 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 

The first query in the union statement may contain an into clause that creates a table to hold the final result set. For example, the following statement creates a table called results that contains the union of tables publishers, stores, and salesdetail:

use mastersp_dboption pubs2, "select into", true
use pubs2
checkpoint
select pub_id, pub_name, city into results 
from publishers 
union 
select stor_id, stor_name, city from stores 
union 
select stor_id, title_id, ord_num from salesdetail 

You can use the into clause only in the first query; if it appears anywhere else, you get an error message.

You can use order by and compute clauses only at the end of the union statement to define the order of the final results or to compute summary values. You cannot use them within the individual queries that make up the union statement. Specifically, you cannot use compute clauses within an insert...select statement.

You can use group by and having clauses within individual queries only; you cannot use them to affect the final result set.

You can also use the union operator within an insert statement. For example:

create table tour (city varchar(20), state char(2))
insert into tour 
    select city, state from stores 
    union 
    select city, state from authors 
drop table tour

You can use the union operator within a create view statement.

You cannot use the union operator on text and image columns.

You cannot use the for browse clause in statements involving the union operator.