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 .. 32 33 34 35 36 37 < 38 > 39 40 41 42 43 44 .. 241 >> Next


SELECT '42434244'xg (GRAPHIC);

Numeric fields are converted to the same representation prior to the conversion.

Table 5-3 shows the data types resulting from the conversions.

Table 5-3

Data Conversion Results

FROM\TO Numeric Date Character Byte Graphic
Numeric numeric date/ errora character error error
Date numeric date character error error
Character numeric/ errorc date/ errorb character error error
Byte error error error byte error
Graphic error error error error graphic

a. If a numeric or character value is converted to date and the value does not represent a valid date, an error is reported.

b. If a numeric or character value is converted to date and the value does not represent a valid date, an error is reported.

c. Valid result only if the character string represents a numeric value.

Teradata RDBMS for UNIX SQL Reference

5-9
Data Definition

GRAPHIC Conversion

You should store numbers as numeric data, not as character data. For example, a table is created with the following code:

CREATE TABLE job AS

(job_code CHAR(6)PRIMARY KEY ,description CHAR(70) ;

)

Subsequently, the following query is made:

SELECT job_code, description FROM job

WHERE job_code = 12 34;

The problem here is that ‘1234’, ‘ 1234’, ‘01234’, ‘001234’, ‘+1234’, and so on, are all valid character representations of the numeric literal value, and the system cannot tell which value to use for hashing. Therefore, the system must do a full table scan to convert all job_code values to their numeric equivalents so that it can do the comparisons. Refer to Appendix F, “Data Conversion,” for further details on data conversion.

5-10

Teradata RDBMS for UNIX SQL Reference
Data Definition

Numeric Data Types

Introduction

Rounding DECIMAL or NUMERIC Data Types

Numeric Data Types

The following date types represent numeric data:

• DATE

• DECIMAL

• NUMERIC

• BYTEINT

• SMALLINT

• INTEGER

• REAL

• DOUBLE PRECISION

• FLOAT

Each of these types can be combined with a range constraint when the data type is defined or modified.

The following table lists Teradata-ANSI synonyms for numeric data types.
ANSI Name Teradata Equivalent
NUMERIC DECIMAL
REAL FLOAT
DOUBLE PRECISION

If a value being inserted does not fit into a field defined as a DECIMAL or NUMERIC data type, the value is rounded. The Teradata RDBMS rounds using the first non-stored value.

IF the value is . . . THEN it is. . .
< 5 rounded down
> 5 rounded up

Teradata RDBMS for UNIX SQL Reference

5-11
Data Definition

Numeric Data Types

Additional considerations come into play when the value is exactly 5.

IF the first non-stored value is exactly 5 and . . .

THEN .

there are no trailing non-zero digits

the last stored value is checked

IF that value is . . . THEN the non-stored value is rounded . . .
odd up
even down

there are trailing rounding behaves as if the first non-stored value

non-zero digits is greater than 5.

Rounding FLOAT, REAL,

DOUBLE PRECISION Data Types

For example, if the number 0.995 is stored as 0.9949999, it is rounded down to 0.99; if it is stored as 0.9950001, it is rounded up to 1.0. Table 5-4 shows the results of inserting values into column NUMBER, which is defined as DECIMAL(3,2).

If a value being inserted does not fit into a field defined as a FLOAT, REAL, or DOUBLE PRECISION data type, the same rounding rule applies, but it is based on the value of the actual number that is stored internally.

Table 5-4

Effects of Rounding

WHEN the value of INSERT is . . . AND the data value in NUMBER is . . . THEN the value is rounded . . . Because the non-stored value is . . .
.014 .01 Down < 5
.015 .02 Up 5 and last stored position is odd
.0151 .02 Up > 5
.024 .02 Down < 5
.025 .02 Down 5 and last stored position is even.
.0251 .03 Up > 5

5-12

Teradata RDBMS for UNIX SQL Reference
Data Definition

DATE Numeric Data Type

DATE Numeric Data Type

The DATE data type identifies a field as a date and simplifies Introduction 5 handling and formatting of date variables.

DATE

IIIMII C CO I I I I I I I I I I I I I I I I I I I I I

FF19A001

The DATE data type handles the following conversions:

• month-to-month

• year-to-year

• leap year

Note: DATE is flagged as not ANSI when the SQL flagger is enabled. Teradata RDBMS DATE is not compatible with the DATE in Intermediate Level ANSI SQL.

The Teradata RDBMS stores each DATE value as an integer, using

DATE is Stored as an the following formula:

Integer (year - 1900) * 10000 + (month * 100) + day

Allowable date values range from AD January 1, 1 to AD December 31, 3500.

This integer value represents the date as YYMMDD (Y=year, M=month, D=day) for dates in the years 1900 to 1999. For example, December 31, 1985 would be stored as the integer 851231; July 4, 1776 is stored as -1239296, and March 30, 2041 is stored as 1410330. Whenever a date is specified as an integer, it is specified in this form.
Previous << 1 .. 32 33 34 35 36 37 < 38 > 39 40 41 42 43 44 .. 241 >> Next