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 .. 56 57 58 59 60 61 < 62 > 63 64 65 66 67 68 .. 241 >> Next

Data Definition

Signed Zone Decimal Conversion

VALUES (:Ext2 (DECIMAL(5), FORMAT '99999S'));

USING Ext2 (CHAR(5))

INSERT INTO Test2 (Col2)

VALUES (:Ext2 (DECIMAL(5), FORMAT '99999S'));

where Ext2 contains the strings ’5678B’ and ’9012L’.

Because Col2 does not have an attached FORMAT phrase, a simple select, such as:

SELECT Col2 FROM Test2;

returns:

Col2

56782.

-90123.

A sign FORMAT phrase must be included in the SELECT statement in order To retrieve the values ’5678B’ and ’9012L’.

It is important to remember this rule when manipulating signed zoned decimal values, especially when using such facilities as a subquery.

If a column is created as a CHARACTER data type:

CREATE TABLE Test3 (Col3 CHAR(5));

and is loaded by selecting, without a sign FORMAT phrase, values from an “unattached” column:

INSERT INTO Test3 (Col3)

SELECT Col2 FROM Test2 ;

the values are actually inserted as:

Col3

5678

-9012

The sign FORMAT phrase must be included in the query specification in order to insert the values ’5678B’ and ’9012L’.

Teradata RDBMS for UNIX SQL Reference

5-101
Data Definition

Using CAST in Data Type Conversions

Introduction

Examples

Example 1

Using CAST in Data Type Conversions

Data type conversions can be done using the following non-ANSI syntax:

------expression--( data_type_list )-----

HH01A019

The ANSI syntax for data type conversion is:

CAST — ( expression —AS — data_type )------

HH01A021

Teradata RDBMS supports an extension of this ANSI syntax:

CAST— ( expression —AS — ansi_sql_data_type )-------

HH01A020

where in the fully compliant form, the SQL-type-list includes only a single data type from the ANSI data types.

CAST is flagged as not Entry Level ANSI in the current release, when the SQL flagger is enabled.

New SQL scripts should avoid using the type list form in data type conversions.

The extended form of CAST should be avoided for any application intended to be ANSI compliant and portable.

The following examples illustrate the use of CAST in data type conversions:

Type List Syntax:

WHERE INTEGER_FIELD = SUBSTR(CHAR_FIELD, 3,3)(INTEGER);

CAST Syntax:

WHERE INTEGER_FIELD = CAST(SUBSTRING(CHAR_FIELD FROM 3 FOR 3) AS INTEGER);

5-102

Teradata RDBMS for UNIX SQL Reference
Data Definition

Using CAST in Data Type Conversions

Type List Syntax:

I—³ IO JLJ

Example 2

SELECT SUBSTR(CHAR_FIELD, 1,2) (INTEGER), SUBSTR(CHAR_FIELD, 3,3)(INTEGER) FROM Tl;

CAST Syntax :

SELECT CAST(SUBSTRING(CHAR_FIELD FROM 1 FOR 2) AS INTEGER),CAST(SUBSTRING (CHAR_FIELD FROM 3 FOR 3) AS INTEGER) FROM Tl;

Teradata RDBMS for UNIX SQL Reference

5-103
Data Definition

Attribute Functions

Introduction

TYPE Function

Attribute Functions

Attribute functions return descriptive information about the operand. The operand need not be a column reference; it can be a general expression that is not evaluated mathematically.

When an attribute function is used in a request, the response returns one row for every data row that meets the search condition.

Note These functions are extensions to ANSI SQL, and will be flagged if the SQL flagger is enabled.

Each function is described in the following sections. Attribute functions include:

• TYPE

• TITLE

• FORMAT

• NAMED

• CHARACTERS

• BYTES

• MCHARACTERS

• OCTET_LENGTH

• CHAR2HEXINT

The CHAR2HEXINT function is described in Chapter 6, “SQL Expressions”, under “String Function: CHAR2HEXINT”.

See also Table 5-2 for a list of data type attributes.

Each function is described in the following passages.

The TYPE(x) function displays the data type defined for the specified operand (x). Information on range constraints is not returned.

The data type is returned as a character string, which is always one of the following:

• BYTEINT

• SMALLINT

• INTEGER

• DECIMAL(n,m)

• FLOAT

• CHAR(n)

• BYTE(n)

5-104

Teradata RDBMS for UNIX SQL Reference
TITLE Function

FORMAT Function

Data Definition

Attribute Functions

• GRAPHIC(n)
• VARCHAR(n)
• VARBYTE(n)
• VARGRAPHIC(n)
• DATE

If, for example, the following TYPE function is used to request the data type defined for the Name and Salary columns of the Employee table:

SELECT TYPE(Name), TYPE(Salary) FROM Employee;

the data returned is:

TYPE(Name) TYPE(Salary)

VARCHAR(12) DECIMAL(8,2) . . .

If the TYPE function is used to request the data type of three columns, defined as graphic, vargraphic, and long vargraphic, respectively, the result is as follows:

TYPE(GColName) TYPE(VGColName) TYPE(LVGColName)

GRAPHIC(n) VARGRAPHIC(n) VARGRAPHIC(n)

In the case of a long vargraphic column, the length returned is the current maximum length of VARGRAPHIC (16,000).

The TITLE(x) function returns a character string for the corresponding attribute of the specified operand (x). The data type is returned as a variable character string of up to 60 characters. In the following example, the TITLE function returns the title defined for the Name column in the Employee table:
Previous << 1 .. 56 57 58 59 60 61 < 62 > 63 64 65 66 67 68 .. 241 >> Next