Books
in black and white
Main menu
Share a book About us Home
Books
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics
Ads

Teradata RDBMS forUNIX SQL Reference - NCR

NCR Teradata RDBMS forUNIX SQL Reference - NCR, 1997. - 913 p.
Download (direct link): teradataforunix1997.pdf
Previous << 1 .. 99 100 101 102 103 104 < 105 > 106 107 108 109 110 111 .. 241 >> Next


Query Expressions (Set Operators)

This query does not use the ALL option; therefore, duplicate rows are dropped. Furthermore, 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.

Example 2

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 returned the following answer set:

***QUERY COMPLETED.IO ROWS FOUND 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 duplicate, and were, therefore, included in the answer set.

This example demonstrates how a poorly formed query can result Example 3 7 in 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

Teradata RDBMS for UNIX SQL Reference

7-47
Queries: The SELECT Statement

Query Expressions (Set Operators)

Rules for Connecting Queries by Set Operators

This query returns the following answer set:

***QUERY COMPLETED.IO 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.

Certain restrictions apply to SELECT statements connected by set operators that may not apply elsewhere. Therefore, in order for a group of queries to be connected by set operators, the queries must satisfy the following rules:

• All SELECT statements in a multiple query must have the same number of expressions.

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

A null expression may be used in a SELECT statement as a place marker for an expression. In the following example:

SELECT EmpNo, NULL (CHAR(5)) FROM Employee

the second expression is null.

• WITH clauses cannot be used in the SELECT statements.

• GROUP BY clauses are allowed in individual SELECT statements and apply only to that SELECT statement and not to the result set.

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

ORDER BY clauses can contain only numeric constants.

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

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

7-48

Teradata RDBMS for UNIX SQL Reference
Queries: The SELECT Statement

Query Expressions (Set Operators)

• The data types of corresponding items in each SELECT statement must be compatible.

For example, if the first field in the first SELECT statement is character data type, then the first field in each succeeding SELECT statement must be character data type.

If a field in the first SELECT statement is numeric, then the corresponding field in succeeding SELECT statements must also be numeric.

It is not necessary that the numeric data types be the same; that is, one field may be defined as INTEGER and the corresponding field may be defined as SMALLINT.

All succeeding corresponding numeric fields will be converted to the numeric data type of the first SELECT statement.

• A set operator cannot be used in subqueries. A set operator cannot be used in the definition of a view.

Teradata RDBMS for UNIX SQL Reference

7-49
Queries: The SELECT Statement

UNION Operator

UNION Operator

The UNION operator combines two or more SELECT statements

Introduction 7 into a single query.

Each query connected by UNION is executed to produce a result consisting of a set of rows. The union should include the same number of columns from each table in each SELECT statement, and the data types of these columns should be the same. All the result sets are then combined into a single result set.

The form of the UNION operator is:

Form7

query expr — UNION —i-1— query factor-------------------->)

lALL-1

FF06A020

Unless the ALL option is used, duplicate rows are eliminated from each result set and from the final result.

If the ALL option is used, duplicate rows are retained for the applicable result set.

The ALL option may be specified for each UNION operator in the query to retain in the final result every occurrence of duplicate rows.
Previous << 1 .. 99 100 101 102 103 104 < 105 > 106 107 108 109 110 111 .. 241 >> Next