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 .. 11 12 13 14 15 16 < 17 > 18 19 20 21 22 23 .. 241 >> Next


Regardless of the number of statements in a macro, the system treats it as a single request.

When you execute a macro, either all of its statements are processed successfully, or none are processed. If a macro fails, it is aborted; any changes made to the data are backed out, and the database is returned to its original state.

As a macro is executed, each of the statements acquires the appropriate read or write lock. A read lock enables users to query the same data at the same time, while disallowing any changes in the data; a write lock prevents queries against data that is being changed.

2-18

Teradata RDBMS for UNIX SQL Reference
Introduction

Three Level Subqueries: Example

Data Handling Fundamentals

Subqueries

Subqueries

Subqueries are nested SELECT statements. They can be used to ask a series of questions in order to arrive at a single answer.

Subqueries may be nested to any number of levels.

Correlated subqueries are described in Chapter 9, “Advanced SQL.”

The following subqueries, nested to three levels, are used to answer the question “Who is Marston’s manager?”:

SELECT Name FROM Employee WHERE EmpNo IN (SELECT MgrNo FROM Department WHERE DeptNo IN

(SELECT DeptNo FROM Employee WHERE Name = 'Marston A') ) ;

The subqueries that pose the questions that lead to the final answer are inverted:

• The third subquery asks the Employee table for the number of Marston’s department.

• The second subquery asks the Department table for the employee number (MgrNo) of the manager associated with this department number.

• The first subquery asks the Employee table for the name of the employee associated with this employee number (MgrNo).

The result:

Name

Watson L

can be obtained using only two levels of subquery, as follows:

SELECT Name FROM Employee WHERE EmpNo IN

(SELECT MgrNo FROM Department, Employee WHERE Employee.Name = 'Marston A'

AND Department.DeptNo = Employee.DeptNo) ;

In this example, the second subquery defines a join of Employee and Department tables.

Teradata RDBMS for UNIX SQL Reference

2-19
Data Handling Fundamentals

Supported Characters

Multinational Character Handling

Supported Characters 2

The Teradata RDBMS can support standard (English language) characters, and characters having diacritical marks (such as a, e, and a).

Multinational character data, including diacritical characters, is provided for each RDBMS system configuration. When the Kanji hash feature is installed, the RDBMS does not support diacritical characters. See Appendix E through Appendix J for more information on character support.

The Teradata RDBMS supports row data containing diacritical characters (diacritical characters cannot be used in statement syntax or to define objects). This feature allows you to insert and retrieve data in a language other than English. It also allows the current character set and collation sequence to be controlled at the session level.

The Teradata RDBMS supports the following multinational characters:

• lowercase and uppercase letters and diacritical characters (a-z and A-Z (including diacriticals a, c, e, i, n, o, u, y and A, C, E, I, N, O, U, Y));

• Arabic numbers (0-9);

• punctuation, graphic, and control characters;

• several unassigned characters.

Change the HashFuncDBC option to International, via the RDBMS Control utility. To save HashFuncDBC change, run the System Initializer (SysInit) utility.

Note: See Teradata RDBMS for UNIX Support Utilities Reference, for more information on the RDBMS Control and System Initializer utilities.

To manipulate data using a multinational character set, the database administrator must populate system tables with the applicable translation codes and a default character set for each client connection. The Teradata RDBMS then must be reset to establish the character sets as currently available for use in a session.

The DBC.CharTranslations, DBC.CharSets, and DBC.HostsInfo views can be queried to review the contents of the system tables associated with multinational character support. If the views are empty or do not exist, multinational character sets are not available.

2-20

Teradata RDBMS for UNIX SQL Reference
Data Handling Fundamentals

Operations on Null

Operations on Null

A null indicates an empty field, an unknown value, or that the field

Introduction 2 has no data.

If an operand of any arithmetic operator or function other than Arithmetic Operator or ZEROIFNULL, NULLIF, COALESCE, or CASE, is null, the result of

Function 2 the operation or function is null.

WHEN the expression is . . . THEN the result is . . .
5 + NULL Null
LOG(NULL) Null
NULLIFZERO(NULL) Null
ZEROIFNULL(NULL)) 0
COALESCE(NULL, 6) 6

If either operand of a comparison operator is null, the result is

Comparison Operator 2 UNKNOWN.

WHEN the expression is . . . THEN the result is . . .
5 = NULL Unknown
5 <> NULL Unknown
Previous << 1 .. 11 12 13 14 15 16 < 17 > 18 19 20 21 22 23 .. 241 >> Next