Data Conversion Factors - FastLoad

Teradata® FastLoad Reference

Product
FastLoad
Release Number
16.20
Published
October 2018
Language
English (United States)
Last Update
2019-02-14
dita:mapPath
ybx1527114222321.ditamap
dita:ditavalPath
ft:empty
dita:id
B035-2411
lifecycle
previous
Product Category
Teradata Tools and Utilities

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 Teradata 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 Teradata Database generates an error message as follows:

    3944: Data length is invalid for the data type.

For details on data types and data conversions, see SQL 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));