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 .. 175 176 177 178 179 180 < 181 > 182 183 184 185 186 187 .. 241 >> Next


Converted to Uppercase defined at creation time for the column being queried.

Character Data Sorted in Ascending Order by Default

Character data is sorted in ascending order unless the DESC (descending) option is included in the SQL request. The results of CASESPECIFIC on sorted results is discussed under the ORDER BY clause of the SELECT statement, in Chapter 7.

Examples

The following examples illustrate the use of SET SESSION COLLATION:

Example 1

A session can be set to ASCII collation as follows:

SET SESSION COLLATION ASCII ;

or

SS COLLATION ASCII ;

Teradata RDBMS for UNIX SQL Reference

8-247
Teradata SQL Syntax Guide

SET SESSION COLLATION

Example 2

Assuming that JobRept contains alphanumeric data, a SELECT that requests a sorted return, as follows:

SELECT EmpNo,JobId FROM JobRept ORDER BY JobId ;

produces, in ascending order:

EmpNo JobId
10201 1001-AP2
10201 1032-AR3
10201 1031-AR2
10004 ENG-0002
10016 ENG-0002
10004 ENG-0003
10003 0E1-0001
10001 PAY-0002

Example 3

If the session is then set to EBCDIC collation:

SET SESSION COLLATION EBCDIC ;

or

SS COLLATION EBCDIC ;

the same request returns:

EmpNo JobId

10004

10016

10004

10003

10001

10201

10201

10201

ENG-000 ENG-0 0 02 ENG-0003 0E1-0001 PAY-0 0 02 1001-AP2 1032-AR3 1031-AR2

If the descending option is added, as follows:

Example 4

´ SELECT EmpNo,JobId FROM JobRept ORDER BY JobId DESC ;

the return is:

EmpNo JobId
10201 1031-AR2
10201 1032-AR3
10201 1001-AP2
10001 PAY-0002
10003 0E1-0001
10004 ENG-0003
10004 ENG-0002
10016 ENG-0002

8-248

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

SHOW MACRO SHOW TABLE SHOW VIEW

SHOW MACRO SHOW TABLE SHOW VIEW

The SHOW MACRO, SHOW TABLE, and SHOW VIEW statement

displays the data definition statement most recently used to create, replace, or modify the specified MACRO, TABLE. or VIEW.

SHOW MACRO, TABLE, or VIEW is flagged as non-ANSI by the SQL flagger when the flagger is enabled.

Syntax

SHOW-

MACRO------macroname -

---TABLE------tname-----

— VIEW — viewname —

FF07A039

where:

Syntax element . . . Displays . . .
macro_name the most recently executed CREATE MACRO or REPLACE MACRO statement for the named macro.
tname a standardized CREATE TABLE statement that would create the referenced table. If the table has been modified using the ALTER TABLE statement, or CREATE INDEX statement, modifications are reflected in the CREATE TABLE statement displayed SHOW TABLE displays all index and constraint information for the table. There is an upper limit of 31,744 characters that SHOW TABLE can display. Since a standard display form is used, the result will generally not be identical to the text used to create the table. Its form is such that if the table were dropped and created using the SHOW TABLE output, the result would be a table with structure identical to the one shown
view_name the most recently executed CREATE VIEW or REPLACE VIEW statement for the named view.

Teradata RDBMS for UNIX SQL Reference

8-249
Teradata SQL Syntax Guide

SHOW MACRO SHOW TABLE SHOW VIEW

Privileges Required

Is Data Secure?

If DATABLOCKSIZE or FREESPACE Has Been Modified

Examples

Example 1

Example 2

To use SHOW MACRO, TABLE, or VIEW, a user must either:

• have a privilege on the macro, table, or view, or a privilege on the database containing it, or

• have SELECT privilege on table RDBMS.TVM (used by database administrators only)

Even database administrators cannot see or alter contents of objects for which they do not have relevant rights, hence the data remains secure.

If the DATABLOCKSIZE or FREESPACE (or both) of the table has been modified using the ALTER TABLE or CREATE TABLE statement, the SHOW TABLE command results in the currently defined data block size or percent freespace (or both) being listed in the response. If the data block size or freespace value is not listed, the default value is in effect. See the description of the “ALTER TABLE” or “CREATE TABLE” statement.

The following examples illustrate the use of SHOW MACRO, TABLE, or VIEW:

The following statement displays the most recently executed definition of the NewEmp macro:

SHOW MACRO NewEmp;

The Teradata RDBMS displays:

REPLACE MACRO NewEmp( name CHAR(2 0) NOT NULL, street CHAR(30), city CHAR(20), number INTEGER, dept SMALLINT DEFAULT 999)

AS ( INSERT INTO Employee (Name, Street, City,

Empno, DeptNo)

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

UPDATE Department SET EmpCount = EmpCount + 1 WHERE DeptNo = :dept ; ) ;

If a SHOW TABLE statement is entered for a table that has been modified, the original CREATE TABLE statement, including all current modifications, is displayed. For example, consider the following statements used to create and subsequently modify the EmpBonus table:

CT Personnel.EmpBonus (EmpNo SMALLINT FORMAT '9(5)'CHECK (EmpNo BETWEEN 10001 AND 32001) NOT NULL,
Previous << 1 .. 175 176 177 178 179 180 < 181 > 182 183 184 185 186 187 .. 241 >> Next