Data Conversion Factors | Teradata FastLoad - 17.10 - Data Conversion Factors - FastLoad

Teradata® FastLoad Reference

Product
FastLoad
Release Number
17.10
Published
June 2021
Last Update
2021-07-01
Content Type
Programming Reference
Publication ID
B035-2411-061K
Language
English (United States)
Be aware of the following when using Teradata FastLoad to perform data conversion:
  • When using the DEFINE command to change the data type specification of source data before inserting it into the Teradata FastLoad table on the database, only one type conversion per column can be specified.
    Teradata FastLoad cannot be used to define a column with an arithmetic expression. For example, Teradata FastLoad will not calculate a monthly salary column from yearly salary data.
  • When using Teradata FastLoad to load and convert data from decimal to character, define the length of the column into which the data will be inserted to be larger than the size of the source data in order to accommodate the sign byte. Otherwise, the database generates an error message as follows:

    3944: Data length is invalid for the data type.

For more information on data types and data conversions, see Teradata Vantage™ - Data Types and Literals, B035-1143.

Valid Data Conversion Example

The following valid example converts character data to integer data, assuming that column b is of type INTEGER:

DEFINE a (char(10)) file= . . . ;
INSERT INTO table1 (b) VALUES (:a(integer)) ;

Valid Redundant Conversion Example

The following valid example converts data in zoned decimal format to type decimal format, assuming that column d is of type DECIMAL (9,2):

DEFINE b (char(9)) file= . . . ;
INSERT INTO Table1 (d) VALUES (:b (decimal(9,2)));
Redundant conversions can be used, as a reminder that a data conversion is taking place.

Invalid Data Conversion Example

The following data conversion example is invalid because it requests two conversions, from character to decimal, and then from decimal to integer, assuming that column b is of type INTEGER:

DEFINE a(char(10)) file= . . . ;
INSERT INTO table1 (b) VALUES (:a (decimal(5,2))) ;

If loading Decimal(5,0) data, the length of the destination character column must be defined as Char(6) instead of Char(5). The following data conversion example is invalid because the column size is not large enough to hold all of the data:

DEFINE a(decimal(5,0)) file = .;
INSERT INTO table (b) VALUES (: a(char(5));