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 .. 48 49 50 51 52 53 < 54 > 55 56 57 58 59 60 .. 241 >> Next

a constant TITLE(x) is a character representation of that constant. The character representation of the constant could be the identical characters or a normalized form of the constant. The following statement: SELECT Name, 12 FROM Employee WHERE EmpNo = 10003; returns 12 as the column heading for the value 12: Name12 Leidner P12
of the form “op y”, where op is a unary (+ or -) or aggregate operator TITLE(x) is ’op’ followed by TITLE(y) The statement: SELECT SUM(Salary) FROM Employee WHERE DeptNo = 700; returns the name of the aggregate operator (SUM) as part of the column title: Sum(Salary) 113,000.00

Teradata RDBMS for UNIX SQL Reference

5-71
Data Definition

TITLE Phrase

IF x is . . . THEN . . . Example
of the form “y op z” TITLE(x) is TITLE(y), followed by “op”, followed by TITLE(z). The statement: SELECT Salary_Loan FROM Employee_Loan WHERE EmpNo = 10004; returns the headings of the column data for which the computation is performed and the operator: Salary Loan 41000.00
an attribute function of the form “fcn (y)” TITLE(x) is fcn(’, followed by TITLE(y), followed by ’). The statement: SELECT FORMAT (Employee.EmpNo); returns the following result: FORMAT(EmpNo) ZZZZ9
of the form “y (datadescription)” , where datadescription does not contain a TITLE phrase TITLE(x) is TITLE(y). The statement: SELECT YrsExp (BYTEINT) FROM Employee WHERE EmpNo = 10 016; returns the following result: YrsExp 20
of the form “y (datadescription)” , where datadescription contains a TITLE phrase TITLE(x) is the title that is specified by the clause. The statement: SELECT Salary (INTEGER, TITLE 'Pay') FROM Employee WHERE EmpNo = 10018; returns the following result: Pay 65000

The number of dashes used to define a column’s width is an attribute of BTEQ. You can modify this display by including a FORMAT phrase in the SELECT statement.

5-72

Teradata RDBMS for UNIX SQL Reference
Data Definition

Default Control Phrase

Introduction

NOT NULL Phrase

Default Control Phrase

A default control phrase determines the action to be taken when you do not supply a value for a field. Default controls apply only to the following:

• Fields defined in CREATE TABLE and ALTER TABLE statements

• Parameters defined in CREATE MACRO and REPLACE MACRO statements

Default controls are not effective for views and expressions.

The following phrases provide default controls:

• NOT NULL

• DEFAULT

• WITH DEFAULT

The default value for a field is null, unless NOT NULL is specified. Allowing nulls can affect query performance; therefore, consider using NOT NULL unless you intend to have null values in the column.

Default values are not effective for views and expressions.

The WITH DEFAULT phrase is mutually exclusive with the NULL and the DEFAULT phrase.

The NOT NULL phrase specifies that the fields of a column must contain a value; they cannot be empty.

For example, in the following column definition, the Name column is defined as follows:

Name VARCHAR(12) NOT NULL,

The NOT NULL specification ensures that every row in the Employee table contains a value in the Name field. An error is returned if a user attempts to insert row data without a name, or to specify NULL in place of a name.

If a NOT NULL phrase is specified for a column in an ALTER TABLE statement, a DEFAULT or a WITH DEFAULT phrase must also be specified unless the table is empty. This ensures that any empty field in that column will be supplied with default values in compliance with the NOT NULL specification.

Teradata RDBMS for UNIX SQL Reference

5-73
Data Definition

Default Control Phrase

DEFAULT Phrase

Table 5-13

Values for DEFAULT Phrase Keywords

The DEFAULT phrase defines the value that is to be inserted in the field when a value is not supplied in an inserted row.

The syntax of the DEFAULT phrase is:

— DEFAULT — constantvalue—

HH01A091

where constant_value is either a keyword or the value of the default.

A default value must be compatible with the data type specified for the column. For example, the phrase INTEGER DEFAuLt 3.5 is illegal.

A default value must not violate any CHECK constraints specified for the column. If a default value would violate a CHECK constraint, the conflict is not recognized at the time the table is defined or altered. The conflict is recognized the first time that an INSERT or UPDATE attempts to enter a NULL, which would be replaced by the default.

A keyword used as a constant_value inserts a string that is already known to the system, such as the date, the time, or the name of the user defining the column.

In an ALTER TABLE statement, the DEFAULT phrase can be used with a keyword to override a previously-defined value. In such cases, the data type of the column must agree with the string inserted by that keyword.

This form of the DEFAULT phrase, the function performed by each keyword, and the corresponding data types are summarized in Table 5-13.
Previous << 1 .. 48 49 50 51 52 53 < 54 > 55 56 57 58 59 60 .. 241 >> Next