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 .. 30 31 32 33 34 35 < 36 > 37 38 39 40 41 42 .. 241 >> Next


The following statement illustrates the use of a request terminator in the body of a macro:

CREATE MACRO Test_Pay (number (INTEGER), name (VARCHAR(12)), dept (INTEGER)

AS ( INSERT INTO Payroll_Test (EmpNo, Name, DeptNo)

VALUES (:number, :name, :dept) ;

UPDATE DeptCount SET EmpCount = EmpCount + 1 ;

SELECT * FROM DeptCount ; )

When entered through BTEQ, the entire CREATE MACRO statement must be terminated.

For example:

CREATE MACRO Test_Pay (number (INTEGER), name (VARCHAR(12)), dept (INTEGER)

AS (INSERT INTO Payroll_Test (EmpNo, Name, DeptNo)

VALUES (:number, :name, :dept) ;

UPDATE DeptCount SET EmpCount = EmpCount + 1 ;

SELECT * FROM DeptCount ; ) ;

Teradata RDBMS for UNIX SQL Reference

4-37
Teradata SQL Lexicon

Request Terminator

4-38

Teradata RDBMS for UNIX SQL Reference
Data Definition

Chapter 5

Data Definition

Teradata RDBMS for UNIX SQL Reference
Data Description Phrases

TeradataR DBMS for UNIX SQL Reference
Data Definition

About This Chapter

About This Chapter

This chapter describes the following topics::

• Data definition?

• Data definition form

• Using data definitions

• ANSI and Teradata mode

• Numeric data types

• Date

• Decimal and NUMERIC

• Byteint

• Integer

• Float, Double Precision, Real

• Character data types

• Char, Varchar, and Long Varchar

• Specifying case

• Character data on a Japanese character site

• Byte/Varbyte data types:

• Kanji graphic data types

• Graphic

• Vargraphic

• CHECK constraint phrase

• FORMAT phrase

• NAMED phrase

• TITLE phrase

• Default control phrase

• COMPRESS phrase

• Data type conversion

• Implicit type conversion rules

• Using CAST in data type conversions

• Attribute functions

• Hash-related expressions

Teradata RDBMS for UNIX SQL Reference

5-1
Data Definition

What Is Data Definition?

What Is Data Definition?

Data definition has the following form:

Data Definition Form

----------------------------------------data type declaration------J------->]

----data type attribute--------

—column storage attribute —

—column constraint attribute —

HH01A014

Note: Data definition must be included when defining a column.

Data definition can be omitted when modifying data.

Each data definition has the following characteristics:

Characteristic Description
Data type declaration See Table 5-1 for a summary.
Data type attributes (optional) See Table 5-2, Table 5-3, and Table 5-4 for a summary of these, and to later in this chapter, “Attribute Functions”.
Column storage attributes (optional) See the ALTER TABLE and CREATE TABLE statements.
Column constraint attributes (optional) See the ALTER TABLE and CREATE TABLE statements. “Column and Table Constraints”, are also described later in this chapter.

Data type attributes control the internal representation of stored data and determine how data is presented for a column or an expression result.

5-2

Teradata RDBMS for UNIX SQL Reference
Data Definition

What Is Data Definition?

Using Data Definition

Data definition can occur in any order In a statement. Data definitions are also referred to as data description phrases.

When used to define the attributes of a data value, also referred to as data type attributes, the data definition immediately follows a column name.

In the following statement, the following columns are assigned attributes using data definitions:

• EmpNo

• Name

• DeptNo

• JobTitle

• Salary

• YrsExp

• DOB

• Sex

• Race

• MStat

• EdLev

• HCap

CREATE TABLE Employee

(EmpNo PRIMARY KEY SMALLINT FORMAT '9(5)' CHECK(EmpNo BETWEEN 1000 AND 32001),

Name VARCHAR(12)NOT CASESPECIFIC NOT NULL,

DeptNo SMALLINT FORMAT '999' CHECK (DeptNo BETWEEN 100 AND 900),

JobTitle VARCHAR(12)NOT CASESPECIFIC,

Salary DECIMAL(8,2) FORMAT 'ZZZ,ZZ9.99'

CHECK (Salary BETWEEN 1.00 AND 999000.00),

YrsExp BYTEINT FORMAT 'Z9' CHECK (YrsExp BETWEEN -99 AND 99), DOB DATE FORMAT 'MMMbDDbYYYY' NOT NULL,

Sex CHAR UPPERCASE NOT NULL,

Race CHAR UPPERCASE,

MStat CHAR UPPERCASE,

EdLev BYTEINT FORMAT 'Z9'

CHECK (EdLev BETWEEN 0 AND 2) NOT NULL,

HCap BYTEINT FORMAT 'Z9' CHECK (HCap BETWEEN -99 AND 99)

INDEX (Name) ;

Teradata RDBMS for UNIX SQL Reference

5-3
Data Definition

Data Type Attributes: ANSI Mode

Data Type Attributes: ANSI Mode

When used to modify the attributes of returned value, the data IntrOduCtiOn definition immediately follows the column being modified. This

usage is non-ANSI.

For example, in the following SELECT statement, a TITLE phrase overrides the default heading (the column name) for EmpNo, and a FORMAT phrase modifies the display format defined for Salary data in the CREATE TABLE statement.

SELECT EmpNo (TITLE 'Emp#'),

Salary (FORMAT '$$$,$$9.99')

FROM Employee;

The expression “data type attributes” is non-ANSI. Also attributes such as TITLE and FORMAT, are non-ANSI. For applications that need to conform to ANSI and to modify attributes of a value, the CAST function should be used. In this case, the modification is restricted to converting to an ANSI defined type. CAST is described later in detail.
Previous << 1 .. 30 31 32 33 34 35 < 36 > 37 38 39 40 41 42 .. 241 >> Next