Queries Connected by Set Operators - Teradata Database

Queries Connected by Set Operators

Certain rules and restrictions apply to SELECT statements connected by set operators that might not apply elsewhere.

Number of Expressions in SELECT Statements

All SELECT statements must have the same number of expressions.

If the first SELECT statement contains three expressions, all succeeding SELECT statements must contain three expressions.

You can use a null expression in a SELECT statement as a place holder for a missing expression.

In the following example, the second expression is null.

   SELECT EmpNo, NULL (CHAR(5)) 

   FROM Employee;

WITH Clause

WITH clauses cannot be used in SELECT statements connected by set operators.

GROUP BY and ORDER BY Clauses

GROUP BY clauses are allowed in individual SELECT statements of a query expression but apply only to that SELECT statement and not to the result set.

ORDER BY clauses are allowed only in the last SELECT statement of a query expression and specify the order of the result set.

ORDER BY clauses can contain only numeric literals.

For example, to order by the first column in your result set, specify ORDER BY 1.

View definitions with set operators can use GROUP BY but cannot use ORDER BY. A SELECT statement applied to a view definition with set operators can use GROUP BY and ORDER BY. The following examples are correct uses of these operations within a view definition:

   REPLACE VIEW v AS 

   SELECT x1,y1 

   FROM table1 

   UNION

   SELECT x2,y2 

   FROM table2;

   SELECT * 

   FROM v 

   ORDER BY 1;

   SELECT SUM(x1), y1 

   FROM v 

   GROUP BY 2;

You can also apply independent GROUP BY operations to each unioned SELECT. The following example demonstrates how to do this:

   REPLACE VIEW v(column_1,column_2) AS

   SELECT MIN(x1),y1 

   FROM table_1 

   GROUP BY 2 

   UNION ALL

   SELECT MIN(x2),y2 

   FROM table_2 

   GROUP BY 2 

   UNION ALL 

   SELECT x3,y3 FROM table_3;

   SELECT SUM(v.column_1) (NAMED sum_c1),column_2

   GROUP BY 2 

   ORDER BY 2;

   SELECT * 

   FROM table_1

   WHERE (x1,y1) IN

   (SELECT SUM(x2), y2 

   FROM table_2

   GROUP BY 2

   UNION

   SELECT SUM(x3), y3 

   FROM table_3

   GROUP BY 2

   );

Table Name in SELECT Statements

Each SELECT statement must identify the table that the data is to come from even if all SELECT statements reference the same table.

Data Type Compatibility

Corresponding fields in each SELECT statement must have data types that are compatible. For example, if the first field in the first SELECT statement is a character data type, then the first field in each succeeding SELECT statement must be a character data type.

Corresponding numeric types do not have to be the same, but they must be compatible. For example, a field in one SELECT statement can be defined as INTEGER and the corresponding field in another SELECT statement can be defined as SMALLINT.

The data types in the first SELECT statement determine the data types of corresponding columns in the result set.

The following table provides details about data type compatibility.

Data Type	Details
Character	Character types in the first SELECT statement determine the length of character strings in the result set. This can lead to truncation of character strings in the result set if the length of a character type in the first SELECT statement is less than the length of corresponding character types in succeeding SELECT statements. The character set of the expression in the first SELECT statement determines the character set for the entire query. For more information, see “Example of How the Character Set is Determined for the Query” on page 1153.
Numeric	Numeric types in the first SELECT statement determine the size of numeric types in the result set. All corresponding numeric fields in succeeding SELECT statements are converted to the numeric data type in the first SELECT statement. This can lead to a numeric overflow error if the size of a numeric type in the first SELECT statement is smaller than the size of corresponding numeric types in succeeding SELECT statements and the values returned by the succeeding statements do not fit into the smaller data type.
TIME TIMESTAMP PERIOD(TIME) PERIOD(TIMESTAMP)	TIME, TIMESTAMP, PERIOD(TIME), and PERIOD(TIMESTAMP) types in the first SELECT statement determine the precision of corresponding columns in the result set. All corresponding fields in succeeding SELECT statements are implicitly converted to the data type in the first SELECT statement. If a corresponding field does not have a time zone and the data type in the first SELECT statement does, the time zone is set to the current session time zone displacement. If the precision of a corresponding field is lower than the precision of the data type in the first SELECT statement, trailing zeros are appended to the fractional digits as needed. If the precision of corresponding fields in succeeding SELECT statements is higher than the precision of the data type in the first SELECT statement, an error is reported.

For examples that show how the length of the character type in the first SELECT statement affects the result set, see “Attributes of a Set Result” on page 1151. For examples that show how the numeric data type in the first SELECT statement affects the result set, see “Example 6: Effect of the Order of SELECT Statements on Data Type” on page 1174.