15.00 - Attributes of a Set Result - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)

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>.