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 .. 124 125 126 127 128 129 < 130 > 131 132 133 134 135 136 .. 241 >> Next


CREATE MACRO Personnel.NewEmp

( Number (SMALLINT FORMAT '9(5)'),

Name (VARCHAR(12)),

Dept (SMALLINT FORMAT '999'),

Position (VARCHAR(12)),

Birthdate (DATE FORMAT 'MMMbDDbYYYY'),

Sex (CHAR(1))

Education (BYTEINT) )

AS (ABORT 'Department 300 not valid'

WHERE :dept = 3 00 ;

INSERT INTO employee (empno, name, deptno, jobtitle, dob, sex, edlev)

VALUES (:number, :name, :dept, :position,

:birthdate, :sex, :education) ; );

Following the ABORT keyword, specify the text of an optional error message, enclosed in apostrophes. This message is displayed on the terminal screen if the macro is aborted for the specified condition. The abort condition (:dept = 300) is identified via the WHERE clause in the ABORT statement.

8-68

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

CREATE TABLE

CREATE TABLE

The CREATE TABLE statement defines the columns, indexing, and other qualities of a new table. It can also define the data block size and percent freespace attributes. The table structure definition is stored in the Data Dictionary.

Teradata RDBMS for UNIX SQL Reference

8-69
Table Level Definition

Teradata SQL Syntax Guide

CREATE TABLE

Syntax

8-70

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

CREATE TABLE

Data Type Declarations

¦ INTEGER------------------

¦SMALLINT-

¦ BYTEINT -

¦ DATE-----

REAL

¦ DOUBLE PRECISION

¦ FLOAT

L ( integer ) —I

DECIMAL-

NUMERIC

—I I— ( integer ³ ³ ) —Ã

'—, integer —'

— CHAR — BYTES GRAPHIC

-VARCHAR¦

I— ( integer) —I

-CHAR VARYING — ---VARBYTE------

VARGRAPHIC LONG VARCHAR

( integer )

-LONG VARGRAPHIC-

FF07B050

Lnot-I

—³- FORMAT —³— I—TITLE—I

Data Type Attributes

NOT NULL-------------------

UPPERCASE-

CASESPECIFIC

NAMED

DEFAULT

- number -USER — DATE — TIME NULL

¦ WITH DEFAULT-

FF07B049

name

Teradata RDBMS for UNIX SQL Reference

8-71
Teradata SQL Syntax Guide

CREATE TABLE

where

Syntax Element...

Description

CREATE

MULTISET TABLE

CREATE SET TABLE

SET and MULTISET are used for duplicate row control.

If there are any uniqueness constraints on any column or set of columns in the table definition, then the table cannot have duplicate rows even if declared MULTISET.

IF this keyword is specified . . . THEN duplicate rows are . . .
SET not allowed. This is compatible with Teradata RDBMS tables from prior releases.
MULTISET allowed, in compliance with the ANSI standard.
neither a check is run on the mode the session is running in. When in Teradata mode, duplicate rows default to SET. When in ANSI mode, duplicate rows default to MULTISET.

tname

Specifies the name of the new table. If the name is not fully qualified, the name of the default database for the current session is assumed.

Create Table Options

Create Table options are an extension to ANSI SQL, and flagged when the SQL flagger is enabled.

[NO] FALLBACK

Specification to choose or not choose duplicate copy protection for the table.

8-72

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

CREATE TABLE

Syntax Element... Description
When FALLBACK is chosen, then duplicate copies of rows in the table are created and stored. The default for this option is established by a CREATE DATABASE, CREATE USER, LOCKING Modifier, or MODIFY USER statement for the database in which the table is to be created.
PROTECTION This keyword can be optionally specified after the FALLBACK keyword. It has no affect whether specified or not.
WITH JOURNAL TABLE = dbname. tname Specification to define or redefine the journal table for the data table being created. tname need not reside in the same database as the data table.

IF a database is . . . THEN . ..
specified it must exist and tname must have been defined as its default journal name.
not specified the default database for the current session is assumed and tname must have been defined as the default journal table.

tname can reside in the same database as the table being created or in a different database.

This clause is required if the CREATE TABLE statement requests some level of journaling, but a default journal table was not defined for the database in which the new table is being created.

If a default journal table was defined for the database, then this clause may be used to override the default

Teradata RDBMS for UNIX SQL Reference

8-73
Teradata SQL Syntax Guide

CREATE TABLE

Syntax Element... Description
NO BEFORE JOURNAL DUAL BEFORE JOURNAL Alters the number of before-change images to be maintained for the table. If the JOURNAL keyword is specified without NO or DUAL, then a single copy of the image is maintained unless FALLBACK is in effect or is also specified. If journaling is requested for a table that uses fallback protection, DUAL images are maintained automatically.
NO AFTER JOURNAL DUAL AFTER JOURNAL LOCAL AFTER JOURNAL NOT LOCAL AFTER JOURNAL Alters the type of image to be maintained for the table; any existing images are not affected until the table is updated. NO, DUAL, LOCAL, or NOT LOCAL: the NO and DUAL options specify the number of afterchange images to be maintained for the table. NOT LOCAL and LOCAL specify whether single after-image journal rows for non-fallback data tables are written on the same virtual AMP (LOCAL) as the changed data rows, or on another virtual AMP in the cluster (NOT LOCAL). See also “CREATE DATABASE”, “Local Journaling”, and “ALTER TABLE” “Journaling: Local”.
Previous << 1 .. 124 125 126 127 128 129 < 130 > 131 132 133 134 135 136 .. 241 >> Next