Decimal field conversions
By default InfoSphere® DataStage® converts decimal fields to and from all numeric data types and to and from string fields. The default rounding method of these conversion is truncate toward zero. However, the modify operator can specify a different rounding method. See rounding type .
The operator can specify fix_zero so that a source decimal containing all zeros (by default illegal) is treated as a valid decimal with a value of zero.
InfoSphere DataStage does not perform range or representation checks of the fields when a source and destination decimal have the same precision and scale. However, you can specify the decimal_from_decimal conversion to force InfoSphere DataStage to perform an explicit range and representation check. This conversion is useful when one decimal supports a representation of zeros in all its digits (normally illegal) and the other does not.
The following table list the conversions involving decimal fields:
Conversion |
Conversion Specification |
---|---|
decimal from decimal | decimalField = decimal_from_decimal[r_type](decimalField) |
decimal from dfloat | decimalField = decimal_from_dfloat[r_type](dfloatField) |
decimal from string | decimalField = decimal_from_string[ r_type](stringField) |
decimal from ustring | decimalField = decimal_from_ustring[r_type](ustringField) |
scaled decimal from int64 | target_field:decimal[p,s] = scaled_decimal_from_int64 [no_warn] (int64field) |
dfloat from decimal | dfloatField = dfloat_from_decimal[fix_zero] (decimalField) |
dfloat from decimal | dfloatField = mantissa_from_decimal(decimalField) |
dfloat from dfloat | dfloatField = mantissa_from_dfloat(dfloatField) |
int32 from decimal | int32Field = int32_from_decimal[r_type, fix_zero](decimalField) |
int64 from decimal | int64Field = int64_from_decimal[r_type, fix_zero](decimalField) |
string from decimal | stringField = string_from_decimal [fix_zero] [suppress_zero](decimalField) |
ustring from decimal | ustringField = ustring_from_decimal [fix_zero] [suppress_zero](decimalField) |
uint64 from decimal | uint64Field = uint64_from_decimal[r_type, fix_zero](decimalField) |
A decimal conversion to or from a numeric field can be specified with any InfoSphere DataStage numeric data type. InfoSphere DataStage performs the necessary modification. For example, int32_from_decimal converts a decimal either to an int32 or to any numeric data type, such as int16, or uint32.
Decfield:decimal[8,2]=scaled_decimal_from_int64(intfield)
where
intfield = 12345678 would set the value of Decfield to 123456.78.The fix_zero specification causes a decimal field containing all zeros (normally illegal) to be treated as a valid zero. Omitting fix_zero causes InfoSphere DataStage to issue a conversion error when it encounters a decimal field containing all zeros. Data Type Conversion Errors discusses conversion errors.
The suppress_zero argument specifies that the returned string value will have no leading or trailing zeros. Examples:
000.100 -> 0.1; 001.000 -> 1; -001.100 -> -1.1
rounding type
You can optionally specify a value for the rounding type (r_type) of many conversions. The values of r_typeare:
- ceil: Round the source field toward positive infinity. This mode corresponds to the IEEE 754 Round Up mode.
- Examples: 1.4 -> 2, -1.6 -> -1
- floor: Round the source field toward negative infinity. This mode corresponds to the IEEE 754 Round Down mode.
- Examples: 1.6 -> 1, -1.4 -> -2
- round_inf: Round or truncate the source field toward the nearest representable value, breaking ties by rounding positive values toward positive infinity and negative values toward negative infinity. This mode corresponds to the COBOL ROUNDED mode.
- Examples: 1.4 -> 1, 1.5 -> 2, -1.4 -> -1, -1.5 -> -2
- trunc_zero (default): Discard any fractional digits to the right of the right-most fractional digit supported in the destination, regardless of sign. For example, if the destination is an integer, all fractional digits are truncated. If the destination is another decimal with a smaller scale, round or truncate to the scale size of the destination decimal. This mode corresponds to the COBOL INTEGER-PART function.
- Examples: 1.6 -> 1, -1.6 -> -1
The diagram shows the conversion of a decimal field to a 32-bit integer with a rounding mode of ceil rather than the default mode of truncate to zero:
The osh syntax for this conversion is:
'field1 = int32_from_decimal[ceil,fix_zero] (dField);'
where fix_zero ensures that a source decimal containing all zeros is treated as a valid representation.