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

In the following SELECT statement, a NAMED phrase associates an arithmetic expression with the name “Projection” so that the calculated column may be more easily referenced in the WHERE clause.

The second form, using the NAMED clause, is flagged as non-ANSI when the SQL flagger is enabled.

SELECT Name, ((Salary + (YrsExp * 200))/12)

(NAMED Projection) FROM Employee WHERE DeptNo = 600 AND Projection < 2500;

The ANSI syntax for Example 3 is shown in Example 2.

Rules

If a column is associated with an explicit tablename, the table is searched for the columnname. Based on the result of the search, one of the following occurs:

IF this thing . . . IS . . . THEN . ..
matching column found in the table the requested information is returned.
not found in the table the NAMED list is searched for the column name.
column name not found in the explicit table name or in the NAMED list a nonexistent column error is reported.

Teradata RDBMS for UNIX SQL Reference

5-67
Data Definition

Naming Columns and Expressions

Naming Differences: AS Versus NAMED

If a column is not associated with an explicit tablename, tables named in the SQL statement are searched and one of the following occurs.

IF this thing . . . IS . . . THEN .. .
matching column found in only one table the requested information is returned.
found in two or more tables an ambiguous column name error is reported.
not found the NAMED list is searched for the column name.
column name not found inn the SQL statement or in the NAMED list a nonexistent column error is reported.

ANSI syntax is similar to the Teradata NAMED clause. The primary difference is that in the NAMED keyword in the Teradata syntax.

NAMED is required in Teradata syntax, while AS is not.

The NAMED clause is enclosed in parentheses immediately following the renamed column.

The NAMED clause is supported as an extension; do not use it for newly developed applications.

5-68

Teradata RDBMS for UNIX SQL Reference
Data Definition

TITLE Phrase

TITLE Phrase

Title Phrase is flagged as non-ANSI when the SQL flagger is Introduction enabled.

The TITLE phrase defines a heading for displayed or printed results that is different from the column name, which is used by default.

A TITLE specification string is limited to 60 characters, and must be enclosed in single quotes.

In statements submitted via BTEQ, a TITLE specification may be up to three lines.

A double slash character (//) defines a line break. You can insert blanks to center lines.

You can use the TITLE phrase in a CREATE TABLE or ALTER TABLE statement to specify a standard heading.

The following example shows a simple use of the TITLE phrase.

Example 1 5

CREATE TABLE Charges, FALLBACK (EmpNo SMALLINT FORMAT '9(5)'

TITLE 'Employee//Id' BETWEEN 10001 AND 32001 NOT NULL,

Proj_Id CHAR(8) TITLE 'Project// Id' NOT NULL,

WkEnd DATE FORMAT 'YY/MM/DD'

TITLE 'Week//Ending',

Hours DECIMAL(4,1) FORMAT 'ZZ9.9'

BETWEEN 0.5 AND 999.5)

PRIMARY INDEX (EmpNo, Proj_Id)

INDEX (Proj_Id);

When a title is specified at column creation time, note that the Example 2 5 defined column name, not the title name, must be specified in

statements that access the column. For example, to retrieve the total hours worked on each project by user Peterson, the project column must be referenced:

SELECT Proj_Id,SUM(Hours) FROM CHARGES WHERE EmpNo = 10001

GROUP BY Proj_Id ORDER BY Proj_Id ;

The return, however, uses the following title headings:

Project

_____Id Sum(Hours)

PAY-0001 9.5

PAY-0002 34.5

A TITLE phrase also can be given in the retrieval statement to override any defined standard.

Teradata RDBMS for UNIX SQL Reference

5-69
Data Definition

TITLE Phrase

For example, in the following SELECT statement, a TITLE phrase provides a more descriptive heading for the DOB column.

SELECT Name, DOB (TITLE 'Birthdate') FROM Employee;

This statement returns:

Name Smith T Newman P Omura H

Birthdate

51/10/31

56/08/29

54/04/24

The multi-line TITLE definition in this statement:

SELECT Name, DOB (TITLE 'Date//Of//Birth')

FROM Employee;

returns:

Date

Of

Birth

51/10/31

56/08/29

54/04/24

Name Smith T Newman P Omura H

5-70

Teradata RDBMS for UNIX SQL Reference
Data Definition

TITLE Phrase

Teradata SQL uses the following rules to generate a title for Title Phrase Rules expression x:

IF x is . . . THEN . . . Example
a column reference with an explicit TITLE phrase that title value is returned. The explicit title for Project.ProjId, as defined in the CREATE TABLE statement for the Project table, is Project//Id. The statement: SELECT Proj_Id FROM Project WHERE Description = 'O/E Batch System'; returns the following result: Project Id OE2-0003
a column reference with no explicit TITLE the column name is returned. The Salary column has no explicit TITLE. The statement: SELECT Salary FROM Employee WHERE EmpNo = 10002; returns the following result: Salary 35,000.00
Previous << 1 .. 47 48 49 50 51 52 < 53 > 54 55 56 57 58 59 .. 241 >> Next