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

Teradata RDBMS forUNIX SQL Reference - NCR

NCR Teradata RDBMS forUNIX SQL Reference - NCR, 1997. - 913 p.
Download (direct link): teradataforunix1997.pdf
Previous << 1 .. 8 9 10 11 12 13 < 14 > 15 16 17 18 19 20 .. 241 >> Next

A view can be compared to a window through which you can see selected portions of a database. A view is used to retrieve portions of one or more tables and/or other views.

Views look like tables; they display data in columns and rows and, in general, may be used as if they were tables. However, only their column definitions are stored. A view does not contain data. It is not assembled until it is referenced by a statement. Some operations used on tables are not valid on views, and other operations are restricted, depending on how the view is defined.

The CREATE VIEW statement defines a view. The statement names the view and its columns, defines a SELECT of one or more columns from one or more underlying tables and/or views, and can include conditional expressions and aggregate operators to limit the row retrieval.

The ALTER TABLE statement changes the structure of an existing table by performing any or all of the following functions:

• Adding and dropping columns.

• Adding column default control, FORMAT, and TITLE attributes.

• Changing column attributes (that do not affect stored data).

• Adding or removing journaling options.

• Adding or removing the FALLBACK option.

• Changing the DATABLOCKSIZE or percent FREESPACE.

• Adding and dropping column and table level constraints.

The table name can be changed via the RENAME statement.

To redefine the primary index for a table, the table must be recreated. A table also must be recreated to change a data type attribute that affects existing data, to define or delete the

Altering Table Structure and Definition

Creating Indexes


Teradata RDBMS for UNIX SQL Reference
Data Handling Fundamentals

Data Definition Statements

Dropping Tables, Views, and Indexes

COMPRESS attribute for an existing column, or to add a column that would exceed the maximum column count.

Refer to the “ALTER TABLE” in Chapter 8, for rules on changing the data type attribute.

Interactively, the SHOW TABLE statement can call up the current table definition, which can then be modified and resubmitted to create a new table.

If the stored data is not affected by incompatible data type changes, an INSERT... SELECT statement can be used to transfer data from the existing table to the new table.

The DROP TABLE and DROP VIEW statements allow you to remove a table or view from a database. The DROP INDEX statement allows you to remove a secondary index.

Teradata RDBMS for UNIX SQL Reference

Data Handling Fundamentals

SQL Requests


Single-Statement Requests

Multi-Statement Requests

Teradata Mode

SQL Requests

A request to the Teradata RDBMS consists of one or more SQL statements, and can span any number of input lines. The Teradata RDBMS can receive and execute statements that are:

• Embedded in an application program that is written in a procedural language.

• Entered interactively through the BTEQ interface.

• Submitted in a BTEQ script as a batch job.

• Submitted through other supported methods (such as ODBC)

A single-statement request consists of a statement keyword followed by one or more expressions, other keywords, clauses, and phrases. A single-statement request is treated as a solitary unit of work.

The form of a single statement request is:

statement -


Refer to Chapter 9, “Advanced SQL,” “Transaction Semantics: Operating in ANSI or Teradata Mode” for more detail.

A multi-statement request consists of two or more statements separated by semicolons.

The form of a multi-statement request is:



Note: Multiple statement requests are flagged as non-ANSI when the SQL flagger is on.

A multi-statement request is treated as an implicit transaction. That is, if an error is found in any statement in the request, then the entire transaction is aborted. Abort processing backs out any changes made to the database as a result of any preceding statements, deletes any associated spooled output, and releases any associated locks. Any remaining statements are bypassed.


Teradata RDBMS for UNIX SQL Reference
Data Handling Fundamentals

SQL Requests

If any error is found in a request, then just the request is aborted and


ANSI Mode 2 not the entire transaction.

A request is considered complete when either an End of Text Complete Requests 2 character or the request terminator is encountered. A request

terminator is a semicolon. It is the last nonblank character on an input line.

A request terminator is optional except when the request appears in a SQL macro, or when it is entered through BTEQ.

A null statement is a statement that is empty except for spaces or Null Statements or SQL comments. The form of a null statement or comment is:


- - comment_text -or

-/* comment text-


Teradata RDBMS for UNIX SQL Reference

Data Handling Fundamentals
Previous << 1 .. 8 9 10 11 12 13 < 14 > 15 16 17 18 19 20 .. 241 >> Next