Attributes of a Set Result
The data type, title, and format clauses contained in the first SELECT statement determine the data type, title, and format information that appear in the final result.
Attributes for all other SELECT statements in the query are ignored.
Example
SELECT level, param, 'GMKSA' (TITLE 'OWNER')
FROM gmksa
WHERE cycle = '03'
UNION
SELECT level, param, 'GMKSA CONTROL'
FROM gmksa_control
WHERE cycle = '03'
ORDER BY 1, 2;
The query returns the following results set:
***QUERY COMPLETED. 5 ROWS FOUND. 3 COLUMNS RETURNED.
LEVEL PARAM OWNER
----- ----- -----
00 A GMKSA
00 T GMKSA
85 X GMKSA
SF A GMKSA
SF T GMKSA
The first SELECT specifies GMKSA, which is CHAR(5)—that data type is then forced on the second SELECT. As a result, GMKSA_CONTROL entries are dropped because the first five characters are the same.
Because this query does not specify the ALL option, duplicate rows are dropped.
Example
In the next query, the SELECT order is reversed:
SELECT level, param 'GMKSA CONTROL' (TITLE 'OWNER')
FROM gmksa_control
WHERE cycle = '03'
UNION
SELECT level, param, 'GMKSA'
FROM gmksa
WHERE cycle = '03'
ORDER BY 1, 2;
This query returns the following answer set:
***QUERY COMPLETED.10 ROWS FOUND. 3 COLUMNS RETURNED.
LEVEL PARAM OWNER
----- ----- -------------
00 A GMKSA
00 A GMKSA CONTROL
00 T GMKSA
00 T GMKSA CONTROL
85 X GMKSA
85 X GMKSA CONTROL
SF A GMKSA
SF A GMKSA CONTROL
SF T GMKSA
SF T GMKSA CONTROL
In this case, because the first SELECT specified ‘GMKSA CONTROL’, the rows were not duplicates and were included in the answer set.
Example
This example demonstrates how a poorly formed query can cause truncation of the results.
SELECT level, param, 'GMKSA ' (TITLE 'OWNER')
FROM gmksa
WHERE cycle = '03'
UNION
SELECT level, param,'GMKSA CONTROL'
FROM gmksa_control
WHERE cycle = '03'
ORDER BY 1, 2;
This query returns the following answer set:
***QUERY COMPLETED.10 ROWS FOUND. 3 COLUMNS RETURNED.
LEVEL PARAM OWNER
----- ----- ------------
00 A GMKSA
00 A GMKSA CONTRO
00 T GMKSA
00 T GMKSA CONTRO
85 X GMKSA
85 X GMKSA CONTRO
SF A GMKSA
SF A GMKSA CONTRO
SF T GMKSA
SF T GMKSA CONTRO
This query returned the expected rows; note, however, that because of the way the name was specified in the first SELECT, there was some truncation.
Example of How the Character Set is Determined for the Query
The character set of the expression in the first SELECT statement determines the character set of the entire query. In the following example, the character set of <char_expression_1> is used as the character set for the entire query:
SELECT <char_expression_1> FROM <table_1>
UNION
SELECT <char_expression_2> FROM <table_2>;
If <char_expression_2> contains characters not included in the character set of <char_expression_1>, an error can result.
For example, if <char_expression_1> is CHARACTER SET LATIN, but <char_expression_2> is CHARACTER SET UNICODE, attempting to translate <char_expression_2> from UNICODE to LATIN results in an error if multi-byte characters are present in <char_expression_2>.