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


Name_______ _________DOB

Inglis C Mar 07 1938

Peterson J Mar 27 1942

To change the date format to an alternate form, change the SELECT

Example 2 to:

SELECT name, dob (FORMAT 'yy-mm-dd') FROM employee WHERE dob BETWEEN 380307 AND '082542'(DATE, FORMAT 'mmddyy')

ORDER BY dob ;

The format specification changes the display to the following:

Name_______ _DOB

Inglis C 38-03-07

Peterson J 42-03-27

Teradata RDBMS for UNIX SQL Reference

5-97
Data Definition

DATE Conversion

Example 3

EXTRACT

To change the display from date format to integer format, change the statement to:

SELECT name, dob(INTEGER) FROM employee WHERE dob BETWEEN 380307 AND 420825 ORDER BY dob ;

or

SELECT name, CAST (dob AS INTEGER) FROM employee WHERE dob BETWEEN 380307 AND 420825 ORDER BY dob ;

and the display becomes:

Name______ DOB

Inglis C 380307

Peterson J 420327

Notice how the change in format in the WHERE statement simplifies the request.

It is perhaps more useful to get the value of the year, month, or day-of-month as integer values than the encoded date. The function:

EXTRACT—(-

-YEAR-

— MONTH —

— DAY —

-FROM — value) ¦

HH01A069

provides the indicated result as an integer. Date, for EXTRACT, must be a value of DATE data type or a literal in the format ‘YYYY-MM-DD’. If a literal is used, the validity of the date is checked as part of the function.

5-98

Teradata RDBMS for UNIX SQL Reference
Data Definition

Signed Zone Decimal Conversion

Signed Zone Decimal Conversion

Introduction

Teradata SQL can convert input data that is in signed zone (external) decimal format to a numeric data type, thus allowing numeric operations to be performed on row values. The column in which the signed zone decimal data is to be stored may be any numeric data type.

A FORMAT phrase incorporating the S sign character filters the data as it passes in and out of the Teradata RDBMS.

The rightmost character of the input data string is assumed to contain the zone (overpunch) bit. The characters representing the various zone-numeric combinations are listed in Table 5-15.

Table 5-15

Signed Zoned Decimal Character Set

Last Character (Input String) Numeric Conversion Last Character (Input String) Numeric Conversion
{ n . . . 0 } -n . . 0
A n . . . 1 J -n . . 1
B n . . . 2 K -n . . 2
C n . . . 3 L -n . . 3
D n . . . 4 M -n . . 4
E n . . . 5 N -n . . 5
F n . . . 6 O -n . . 6
G n . . . 7 P -n . . 7
H n . . . 8 Q -n . . 8
I n . . . 9 R -n . . 9

The sign FORMAT phrase can be included in a CREATE TABLE or ALTER TABLE statement when the column is defined, or in the INSERT statement when the data is loaded. The chosen method depends on how the stored value is to be used.

When a sign FORMAT phrase is specified at column creation time, it is considered attached to the column because it translates data at the column level; that is, both when the data is loaded and when it is retrieved.

If field values are normally retrieved for manipulation as numeric data, the sign FORMAT phrase should not be used at column creation time.

Using FORMAT in CREATE TABLE

When the FORMAT phrase is used in the CREATE TABLE statement, as follows:

CREATE TABLE Test1 (Col1 DECIMAL(4) FORMAT '9999S');

Teradata RDBMS for UNIX SQL Reference

5-99
Data Definition

Signed Zone Decimal Conversion

Using Another FORMAT in the SELECT Statement

If FORMAT is Not Attached to the Column

then zoned input character strings can be loaded with standard INSERT statements, whether the data is defined:

INSERT INTO Test1 (Col1) VALUES ('123J');

or read from an client system data record via the USING modifier:

USING Ext1 (CHAR(4))

INSERT INTO Test1 (Col1)

VALUES (:Ext1);

The data record contains the string ’123J’.

Subsequently, a simple select, such as:

SELECT Col1 FROM Test1;

returns:

Col1

123J

To override an attached format, another FORMAT phrase is needed in the retrieval statement. Using the preceding table, one of the two following statements must be used to retrieve the numeric value:

SELECT Col1 (FORMAT '+9999') FROM Test1;

or

SELECT CAST (Col1 AS INTEGER) FROM Test1;

The result is as follows.

Col1

-1231

If the format is not attached to the column, the sign FORMAT phrase must be used each time signed zoned decimal data is loaded and each time the row value is to be retrieved in signed zoned decimal format.

If a table is defined as:

CREATE TABLE Test2 (Col2 DECIMAL(5));

then the sign FORMAT phrase must be included whenever signed zoned decimal strings are inserted, whether defined:

INSERT INTO Test2 (Col2)

VALUES ('5678B' (DECIMAL(5), FORMAT '99999S'));

INSERT INTO Test2 (Col2)

VALUES ('9012L' (DECIMAL(5), FORMAT '99999S'));

or read from a client system data record:

USING Ext2 (CHAR(5))

INSERT INTO Test2 (Col2)

5-100

Teradata RDBMS for UNIX SQL Reference
Previous << 1 .. 55 56 57 58 59 60 < 61 > 62 63 64 65 66 67 .. 241 >> Next