Table of Contents

Search

  1. Preface
  2. The Transformation Language
  3. Constants
  4. Operators
  5. Variables
  6. Dates
  7. Functions
  8. Creating Custom Functions
  9. Custom Function API Reference

Transformation Language Reference

Transformation Language Reference

TRUNC (Numbers)

TRUNC (Numbers)

Truncates numbers to a specific digit. You can also use TRUNC to truncate dates.

Syntax

TRUNC(
numeric_value
[,
precision
] )
The following table describes the arguments for this command:
Argument
Required/
Optional
Description
numeric_value
Required
Numeric datatype. Passes the values you want to truncate. You can enter any valid transformation expression that evaluates to a Numeric datatype.
precision
Optional
Can be a positive or negative integer. You can enter any valid transformation expression that evaluates to an integer. The integer specifies the number of digits to truncate.
If
precision
is a positive integer, TRUNC returns
numeric_value
with the number of decimal places specified by
precision
. If
precision
is a negative integer, TRUNC changes the specified digits to the left of the decimal point to zeros. If you omit the
precision
argument, TRUNC truncates the decimal portion of
numeric_value
and returns an integer.
If you pass a decimal
precision
value, the
PowerCenter Integration Service
rounds
numeric_value
to the nearest integer before evaluating the expression.
When you run a session in high precision mode, use the ROUND function before truncating.
For example, suppose the following expression is used to truncate the values in the QTY port:
TRUNC ( QTY / 15 )
When the value for QTY = 15000000, the session returns the value 999999. The expected result is 1000000.
At run time, the
PowerCenter Integration Service
evaluates the constant part of the expression and then the variable part.
In the above expression, QTY is the variable value and (1/15) is the constant value.
When QTY = 15000000, the expression is evaluated as follows:
TRUNC ( 15000000 * (1/15) TRUNC ( 15000000 * (1/15) = TRUNC ( 15000000 * 0.0666666666666666) = TRUNC ( 15000000 * 0.0666666666666666) = TRUNC ( 999999.99999999) = 999999
If you use the ROUND function before truncating, the expression is evaluated as follows:
TRUNC (ROUND (QTY/15, .99999999999999999999999999)).

Return Value

Numeric value.
NULL if one of the arguments is NULL.
If the return value is Decimal with precision greater than 15, you can enable high precision to ensure decimal precision up to 28 digits.

Examples

The following expressions truncate the values in the Price port:
TRUNC( PRICE, 3 )
PRICE
RETURN VALUE
12.9995
12.999
-18.8652  
-18.865
56.9563
56.956
15.9928
15.992
NULL
NULL
TRUNC( PRICE, -1 )
PRICE
RETURN VALUE
12.99
10.0
-187.86  
-180.0
56.95
50.0
1235.99
1230.0
NULL
NULL
TRUNC( PRICE )
PRICE
RETURN VALUE
12.99
12.0
-18.99  
-18.0
56.95
56.0
15.99
15.0
NULL
NULL