Table of Contents

Search

  1. Preface
  2. The Transformation Language
  3. Constants
  4. Operators
  5. Variables
  6. Dates
  7. Functions

Transformation Language Reference

Transformation Language Reference

TO_DECIMAL

TO_DECIMAL

Converts a string or numeric value to a decimal value. TO_DECIMAL ignores leading blanks.

Syntax

TO_DECIMAL(
value
[,
scale
] )
The following table describes the arguments for this command:
Argument
Required/
Optional
Description
value
Required
Must be a string or numeric datatype. Passes the values you want to convert to decimal values. You can enter any valid transformation expression.
scale
Optional
Must be an integer literal between 0 and 28, inclusive. Specifies the number of digits allowed after the decimal point. If you omit this argument, the function returns a value with the same scale as the input value.
If you pass a decimal to the TO_DECIMAL function to cast the decimal to a decimal with a different scale, the scale argument is a maximum and cannot extend scale. For example, if the scale argument is 5 and the scale of the input value is 6, the fractional digits are truncated. If the scale of the input value is 4, the fractional digits remain the same.

Return Value

Decimal of precision and scale between 0 and 28, inclusive.
NULL if a value passed to the function is NULL.
If the value passed to the function contains data that is not valid for a decimal value, the Data Integration Service marks the row as an error row.
If the return value is Decimal with precision greater than 15, you can enable high precision to ensure decimal precision up to 28 digits.

Example

This expression uses values from the port IN_TAX. IN_TAX is a String data type with precision of 44 digits. RETURN VALUE is a Decimal data type with a precision of 28 and scale of 3:
TO_DECIMAL( IN_TAX, 3 )
IN_TAX
RETURN VALUE
'15.6789'
15.679
'60.2'
60.200
'118.348'
118.348
NULL
NULL
'A12.3Grove'
Error. Integration Service skips this row.
'711A1'
Error. Integration Service skips this row.
'1234567890.123'
1234567890.123
'123456789012345678901234567890.123'
Error. Integration Service skips this row.
'1234567890123456789012345678901234567890.123
Error. Integration Service skips this row.

0 COMMENTS

We’d like to hear from you!