Function Reference > Datatype reference > Transformation datatypes
  

Transformation datatypes

The following table describes the transformation datatypes:
Datatype
Size in bytes
Description
Bigint
8 bytes
-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
Precision of 19, scale of 0
Integer value.
Binary
Precision
1 to 104,857,600 bytes
You cannot use binary data for flat file sources.
Date/Time
16 bytes
Jan 1, 0001 A.D. to Dec 31, 9999 A.D.
Precision of 29, scale of 9
(precision to the nanosecond)
Combined date/time value.
Decimal
8 bytes (if high precision is off or precision is greater than 28)
16 bytes (if precision <= 18 and high precision is on)
20 bytes (if precision > 18 and <= 28)
Decimal value with declared precision and scale. Scale must be less than or equal to precision.
Precision 1 to 28 digits, scale 0 to 28
Double
8 bytes
Double-precision floating-point numeric value.
You can edit the precision and scale. The scale must be less than or equal to the precision.
Integer
4 bytes
-2,147,483,648 to 2,147,483,647
Precision of 10, scale of 0
Integer value.
Nstring
Unicode mode: (precision + 1) * 2
ASCII mode: precision + 1
1 to 104,857,600 characters
Fixed-length or varying-length string.
Ntext
Unicode mode: (precision + 1) * 2
ASCII mode: precision + 1
1 to 104,857,600 characters
Fixed-length or varying-length string.
Real
8 bytes
Precision of 7, scale of 0
Double-precision floating-point numeric value.
Small Integer
4 bytes
-32,768 and 32,767
Precision of 5, scale of 0
Integer value.
String
Unicode mode: (precision + 1) * 2
ASCII mode: precision + 1
1 to 104,857,600 characters
Fixed-length or varying-length string.
Text
Unicode mode: (precision + 1) * 2
ASCII mode: precision + 1
1 to 104,857,600 characters
Fixed-length or varying-length string.

Integer datatypes

You can pass integer data from sources to targets and perform transformations on integer data. Data Integration supports Bigint, Integer, and Small Integer datatypes.
The transformation integer datatypes represent exact values.

Integer values in calculations

When you use integer values in calculations, Data Integration sometimes converts integer values to floating-point numbers before it performs the calculation.
For example, to evaluate MOD( 12.00, 5 ), Data Integration converts the integer value "5" to a floating-point number before it performs the division operation. Data Integration converts integer values to double or decimal values depending on how you set the Enable High Precision advanced session property in the mapping task.
Data Integration converts integer values in the following arithmetic operations:
Operation
High Precision Disabled
High Precision Enabled
Functions and calculations that cannot introduce decimal points.
For example, integer addition, subtraction, and multiplication, and functions such as CUME and SUM.
No conversion.
However, if the calculation produces a result that is out of range, Data Integration writes a row error.
Decimal
Non-scientific functions and calculations that can introduce decimal points.
For example, integer division, and functions such as AVG, MEDIAN, and PERCENTILE.
Double
Decimal
All scientific functions and the EXP, LN, LOG, POWER, and SQRT functions.
Double
Double
The transformation Double datatype supports precision of up to 15 digits, while the Bigint datatype supports precision of up to 19 digits. Therefore, precision loss can occur in calculations that produce Bigint values with precision of more than 15 digits.
For example, an expression transformation contains the following calculation:
POWER( BIGINTVAL, EXPVAL )
Before it performs the calculation, Data Integration converts the inputs to the POWER function to double values. If the BIGINTVAL field contains the Bigint value 9223372036854775807, Data Integration converts this value to 9.22337203685478e+18, losing the last 4 digits of precision. If the EXPVAL field contains the value 1.0 and the result field is a Bigint, the calculation produces a row error because the result, 9223372036854780000, exceeds the maximum Bigint value.
When you use an Integer datatype in a calculation that can produce decimal values and you enable high precision, Data Integration converts the integer values to decimal values.
For transformations that support the Decimal datatype with precision up to 28 digits, precision loss does not occur in a calculation unless the result produces a value with precision greater than 28 digits in high precision mode. In this case, Data Integration stores the result as a double. If the field precision is less than or equal to 28 digits and the result produces a value greater than 28 digits in high precision mode, Data Integration rejects the row.

Integer constants in expressions

Data Integration interprets constants in an expression as floating-point values, even if the calculation produces an integer result.
For example, in the expression INTVALUE + 1000, Data Integration converts the integer value "1000" to a double value if high precision is not enabled. It converts the value 1000 to a decimal value if high precision is enabled. To process the value 1000 as an integer value, create a variable field with an Integer datatype to hold the constant, and modify the expression to add the two fields.

NaN values

NaN (Not a Number) is a value that is usually returned as the result of an operation on invalid input operands, especially in floating-point calculations. For example, when an operation attempts to divide zero by zero, it returns a NaN result.
Operating systems and programming languages may represent NaN differently. For example, the following list shows valid string representations of NaN:
Data Integration converts QNAN values to 1.#QNAN on Win64EMT platforms. 1.#QNAN is a valid representation of NaN.
If an expression in an elastic mapping evaluates to a NaN result, the return value for the expression is blank in the output.

Convert string values to integer values

When Data Integration performs implicit conversion of a string value to an integer value, it truncates the data at the first non-numeric character.
For example, you link a string field that contains the value "9,000,000,000,000,000,000.777" to a Bigint field. Data Integration converts the string to the Bigint value 9,000,000,000,000,000,000.

Write integer values to flat files

When writing integer values to a fixed-width flat file, the file writer does not verify that the data is within range.
For example, the file writer writes the result 3,000,000,000 to a target Integer column if the field width of the target column is at least 13. The file writer does not reject the row because the result is outside the valid range for Integer values.

Binary datatype

If a mapping includes binary data, set the precision for the transformation binary datatype so that Data Integration can allocate enough memory to move the data from source to target.
You cannot use binary datatypes for flat file sources.

Date/Time datatype

The Date/Time datatype handles years from 1 A.D. to 9999 A.D. in the Gregorian calendar system. Years beyond 9999 A.D. cause an error.
The Date/Time datatype supports dates with precision to the nanosecond. The datatype has a precision of 29 and a scale of 9. Some native datatypes have a smaller precision. When you import a source that contains datetime values, the import process imports the correct precision from the source column. For example, the Microsoft SQL Server Datetime datatype has a precision of 23 and a scale of 3. When you import a Microsoft SQL Server source that contains Datetime values, the Datetime columns in the mapping source have a precision of 23 and a scale of 3.
Data Integration reads datetime values from the source to the precision specified in the mapping source. When Data Integration transforms the datetime values, it supports precision up to 29 digits. For example, if you import a datetime value with precision to the millisecond, you can use the ADD_TO_DATE function in an Expression transformation to add nanoseconds to the date.
If you write a Date/Time value to a target column that supports a smaller precision, Data Integration truncates the value to the precision of the target column. If you write a Date/Time value to a target column that supports a larger precision, Data Integration inserts zeroes in the unsupported portion of the datetime value.

Decimal and double data types

You can pass decimal and double data from sources to targets and perform transformations on decimal and double data.
Data Integration supports the following data types:
Decimal
Precision 1 to 28 digits, scale 0 to 28. You cannot use decimal values with scale greater than precision or a negative precision. Transformations display any range that you assign to a decimal data type, but Data Integration supports precision only up to 28 digits.
When you enable high precision and the field precision is greater than 28 digits, Data Integration stores the result as a double.
Double
Double-precision floating-point numeric value.
You can edit the precision and scale. The scale must be less than or equal to the precision.

Decimal and double values in calculations

Precision loss can occur with decimal and double data types in a calculation when the result produces a value with a precision greater than the maximum.
If you disable high precision, Data Integration converts decimal values to double. Precision loss occurs if the decimal value has a precision greater than 15 digits. For example, you have a mapping with decimal (20,0) that passes the number 40012030304957666903. If you disable high precision, Data Integration converts the decimal value to double and passes 4.00120303049577 x 1019.
For transformations that support decimal data type of precision up to 28 digits, use the decimal data type and enable high precision to ensure precision of up to 28 digits.
Precision loss does not occur in a calculation unless the result produces a value with precision greater than the maximum allowed digits. In this case, Data Integration stores the result as a double.
Do not use the double data type for data that you use in an equality condition, such as a lookup or join condition.
The following table lists how Data Integration handles decimal values based on how the Enable High Precision advanced session property is set:
Field Data type
Precision
High Precision Disabled
High Precision Enabled
Decimal
0 to 15
Decimal
Decimal
Decimal
15 to 28
Double
Decimal
Decimal
Over 28
Double
Double
When you enable high precision, Data Integration converts numeric constants in any expression function to decimal. If you do not enable high precision, Data Integration converts numeric constants to double.
You can ensure the maximum precision for numeric values greater than 28 or 38 digits depending on the transformation. Before you perform any calculations or transformations with the transformation functions, truncate or round any large numbers.

Rounding methods for double values

Due to differences in system run-time libraries and the computer system where the database processes double datatype calculations, the results may not be as expected. To provide calculation results that are less susceptible to platform differences, Data Integration stores the 15 significant digits of double datatype values.
The double datatype conforms to the IEEE 794 standard. Changes to database client library, different versions of a database or changes to a system run-time library affect the binary representation of mathematically equivalent values. Also, many system run-time libraries implement the round-to-even or the symmetric arithmetic method. The round-to-even method states that if a number falls midway between the next higher or lower number it round to the nearest value with an even least significant bit. For example, with the round-to-even method, 0.125 is rounded to 0.12. The symmetric arithmetic method rounds the number to next higher digit when the last digit is 5 or greater. For example, with the symmetric arithmetic method 0.125 is rounded to 0.13 and 0.124 is rounded to 0.12.
Data Integration stores the 15 significant digits of double datatype values. For example, if a calculation on Windows returns the number 1234567890.1234567890, and the same calculation on UNIX returns 1234567890.1234569999, Data Integration converts this number to 1234567890.1234600000.

String datatypes

The transformation string datatypes include Nstring, Ntext, String and Text. Although the Nstring, Ntext, String, and Text datatypes support the same precision up to 104,857,600 characters, Data Integration uses String to move string data from source to target and Text to move text data from source to target.
Because some databases store text data differently than string data, Data Integration needs to distinguish between the two types of character data. If the source displays String, set the target column to String. Likewise, if the source displays Text, set the target column to Text, Long, or Long Varchar, depending on the source.
In general, the smaller string datatypes, such as Char and Varchar, display as String in the source, Lookup transformation, and SQL transformation, while the larger text datatypes, such as Text, Long, and Long Varchar, display as Text in the source.
Use Nstring, Ntext, String, and Text interchangeably within transformations. However, in the source, Lookup transformation, and SQL transformation, the target datatypes must match. The database drivers need to match the string datatypes with the transformation datatypes, so that the data passes accurately. For example, Nchar in a lookup table must match Nstring in the Lookup transformation.