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 .. 52 53 54 55 56 57 < 58 > 59 60 61 62 63 64 .. 241 >> Next


Numeric overflows are handled differently depending on whether you are running ANSI or Teradata mode and whether you are running in FIELD MODE or not.

FIELD MODE is not ANSI-compatible. When results for a field value that is a result of a numeric overflow of a numeric or decimal data type are returned in FIELD MODE, what is returned is asterisks (‘***’) rather than an error message.

RECORD and INDICATOR modes do not behave in this manner and return an error message.

Teradata RDBMS for UNIX SQL Reference

5-85
Data Definition

Explicit Data Type Conversion with a Data Definition Phrase

Explicit Data Type Conversion with a Data Definition Phrase

The data definition phrase is:

Introduction5

---value — (data definition )-

HH01A075

where data definition includes a data type declaration. and serves to redefine the data.

This syntax is flagged as non-ANSI when the SQL flagger is enabled. If data definition includes a data type declaration, then the data is converted at runtime. At that time, a data conversion or range check error may occur.

For any kind of data type conversion of the form: col_name-----(data_defnition)-

HH01A013

Where the item that includes a data type declaration is an operand of a complex expression, you must enclose the appropriate entities in parentheses, or use the CAST syntax.

New applications should use the CAST function to perform conversions to ensure ANSI compatibility.

To evaluate correctly an expression of the form:

Example5

1 columnname (integer) + variable

You could enter the expression as follows:

(columnname (integer)) + variable

or, preferably as:

CAST (columnname AS INTEGER) + variable

See the section “CAST in Data Type Conversion”.

5-86

Teradata RDBMS for UNIX SQL Reference
Introduction

Data Definition

Numeric Conversion

Numeric Conversion

Numeric conversion is performed on numeric data, producing either a numeric or a character data type result.

Teradata RDBMS for UNIX SQL Reference

5-87
Data Definition

Numeric to Numeric

Numeric to Numeric

Introduction

The form for converting numeric data to data of a different numeric type is:

— CAST — (numeric_expression — AS — numeric_data_defnition) —

or — numeric_expression (numeric_data_defnition) —

HH01A085

Example 1

Example 2

CAST (IntegerField AS DECIMAL (7,2))

Conversion of numeric to INTEGER, BYTEINT, or SMALLINT truncates any decimal portion. Conversion to DECIMAL produces a rounded result. If a range violation occurs, the operation may fail.

Some examples of numeric conversions are:

Value

20000.99

20000.99

20000.99 200000

Converted To

INTEGER

DECIMAL(6,1)

DECIMAL(4,1)

SMALLINT

Result

20000

20001.0

error

error

Although the FORMAT phrase cannot be used to change the underlying data type defined for a column, the phrase may be used to change the display for a numeric value.

For example, if the field values for columns Wholesale and Retail, both defined as DECIMAL(7,2), are 12467.75 and 21500.50, respectively, the result of the expression:

CAST (Wholesale - Retails AS FORMAT '-99999')

is:

-09033.

A FORMAT phrase does not affect data that is returned to the client system in Record Mode (client system internal format). In the previous example, the value returned to the client system is still in packed decimal format (for example, -9032.75).

Note: The use of FORMAT in CAST is a non-ANSI extension.

5-88

Teradata RDBMS for UNIX SQL Reference
Data Definition

Numeric to Character

Numeric to Character

The form for converting numeric type data to a character data type Introduction 5 is:

— CAST — (numeric_expression — AS —character_data_definition) — or — numeric_expression — (character_data_defnition) —

HH01A086

The process followed for the CAST function is described in the following table.

Stage Process
1 Convert the numeric value to a character string using the default or specified format for the numeric value.
2 Trim leading and trailing blanks.
3 Extend to the right as required.
4 If truncation of non-blank characters is required, report truncation error.

The CAST operation, differs from the Teradata SQL conversion as follows:

• Results are left justified. Column display will not be aligned.

• Truncation of significant data gives an error.

Use of Teradata syntax i.e., not using CAST) for explicit conversion of numeric to character data, requires caution. The process followed is described in the following table.

Stage Process
1 Convert the numeric value to a character string using the default or specified FORMAT for the numeric value.
2 Leading and trailing blanks are not truncated.
3 Extend to the right with blanks if required, or truncate from the right if required, to conform to the target length specification.
4 If non-blank characters are truncated, no error is reported.

Teradata RDBMS for UNIX SQL Reference
Previous << 1 .. 52 53 54 55 56 57 < 58 > 59 60 61 62 63 64 .. 241 >> Next