Teradata RDBMS forUNIX SQL Reference - NCR

The following statement is not valid in ANSI SQL since Tl.Yrs is a CHAR(2) value, added to a numeric value, to be assigned to a numeric value, therefore it is flagged if the SQL flagger is enabled.

Field Age of T2 is SMALLINT Field Yrs of T1 is CHAR(2)

with character representations of numeric values.

UPDATE T2 SET Age = T1.Yrs + 5;

This is not evident in the syntax of the source statement, but becomes evident when the dictionary information for tables Tl and T2 is accessed.

SQL performs implicit type conversions on expressions before any operation is performed. The rules for implicit conversion are as follows:

 Numeric items are converted to the same numeric type before any arithmetic or comparison operation is performed. The result returned is of this same underlying type. Refer to Table 5-3 and Table 5-5 for information about defaults of arithmetic and aggregate results.

Conversions performed for numeric expressions are as follows:

 BYTEINT + DECIMAL(2,1) = DECIMAL(18,1)

For example, 1 +1.5 = 2.5

 SMALLINT + DECIMAL(3,2) = DECIMAL(18,2)

For example, 1000 + 7.08 = 1007.08

 INTEGER * DECIMAL(2,1) = DECIMAL(18,1)

For example, 60000 * 1.1 = 66000.0

 FLOAT * BYTEINT = FLOAT For example, 1.0E6 * 2 = 2.0E6

5-82

Teradata RDBMS for UNIX SQL Reference
Data Definition

Implicit Type Conversion Rules

 Comparisons of strings (fixed-length or variable-length) require operands of equal length. The following table shows that the shorter string is converted by being filled in on the right.

Expression Converted To Result Is

x=x  xfe=x  True

x=xx xb=xx False

where bis a blank.

 If a character string is present in an expression that requires a numeric operand, it is read as a formatted numeric and is converted to a floating-point (FLOAT) value, with an implied FORMAT of:

-$$$,$$$,$$$.999999E-999

An explicit FORMAT phrase or TYPE phrase can override this implicit format. A blank or empty character string (zero length) is interpreted as having a numeric value of zero.

Expression Converted To Result Is

1.1*'$20.00' 1.1E0*2.0E1 2.2E1

'2'+'2' 2.0E0+2.0E0 4.0E0

'A' + 2 - error

 If a column or parameter of numeric data type is specified with a string value, the string is again assumed to be a formatted numeric. For example, the following INSERT statement specifies the Salary as a numeric string:

INSERT INTO Employee (EmpNo, Name, Salary)

VALUES (10022, 'Clements D', '$38,000.00');

The conversion to numeric type removes editing symbols. When selected, the salary data contains only the special characters allowed by the FORMAT phrase for Salary in the CREATE TABLE statement:

Salary

38,000.00

Teradata RDBMS for UNIX SQL Reference

5-83
Data Definition

CAST in Data Type Conversion

Introduction

Using CAST with Character and Graphic Types

CAST in Data Type Conversion

The ANSI SQL syntax for CAST is:

CAST ( expression AS  ansi_sql_data_type )---------

HH01A020

This can be used to convert data types in ANSI SQL compliant statements, and Teradata SQL statements.

The Teradata RDBMS version of this syntax is more general in that it allows both a type declaration and data attribute.

CAST------( expression AS data_definition )-----------

HH01A018

The function is ANSI SQL compliant only if the data definition consists of an ANSI SQL data type declaration.

Refer to the Data Type Declaration options table, Table 5-1, defined under Data Types, at the beginning of this chapter.

CAST can be used for character and graphic strings.

IF the string . . . THEN . . .
expands pad it with the pad character.
contracts Japanese character truncation rules apply.

The following rules apply to character and graphic strings (including Japanese character and graphic strings):

ANSI Mode
This type of string . . . Is truncated of . . .
Character trailing single byte character spaces to achieve the desired length. Truncation of other characters, or part of a multibyte character, returns an error
Graphic trailing double-byte zeroes to achieve the desired length
All others Nothing. Returns an error. If non-pad characters are removed from a string, an error is returned
Teradata Mode
Character trailing single byte character spaces to achieve the desired length. Truncation of other characters, or part of a multibyte character, returns an error.
Graphic trailing double-byte zeroes to achieve the desired length.
All others Nothing. Returns an error. If non-pad characters are removed from a string, an error is returned. When truncation occurs but no error is returned, it is because the rules used prior to this release are being supported.