This topic contains sections marked as revised for this release

WebSphere Message Brokers
File: ak05380_
Writer: Bill Oppenheimer

Reference topic

This build: July 31, 2007 21:32:01

ROUND function

The ROUND numeric function rounds a supplied value to a given number of places.

Syntax

Notes:
  1. If you do not specify MODE, a value of ROUND_HALF_EVEN is used.

If precision is a positive number, source_number is rounded to precision places right of the decimal point. If precision is negative, the result is source_number rounded to the absolute value of precision places to the left of the decimal point.

source_number can be any built-in numeric data type; precision must be an integer. The result is of the same data type as the source_number parameter unless source_number is NULL, in which case the result is NULL.

This means that the result of the function is:
  • INTEGER if source_number is INTEGER
  • FLOAT if source_number is FLOAT
  • DECIMAL if source_number is DECIMAL
When rounding, the banker's or half-even symmetric rounding rules are used by default, unless a RoundingMode is specified.

RoundingMode

RoundingMode can take one of the following values:
ROUND_UP
Round away from zero. Always increments the digit prior to a nonzero discarded fraction. This rounding mode never decreases the magnitude of the calculated value.
ROUND_DOWN
Round towards zero. Never increments the digit prior to a discarded fraction, that is, truncates. This rounding mode never increases the magnitude of the calculated value.
ROUND_CEILING
Round towards positive infinity. If the decimal is positive, behaves as for ROUND_UP; if negative, behaves as for ROUND_DOWN. This rounding mode never decreases the calculated value.
ROUND_FLOOR
Round towards negative infinity. If the decimal is positive, behaves as for ROUND_DOWN; if negative, behaves as for ROUND_UP. This rounding mode never increases the calculated value.
ROUND_HALF_UP
Round towards "nearest neighbor" unless both neighbors are equidistant, in which case round up. Behaves as for ROUND_UP if the discarded fraction is greater than, or equal to, 0.5; otherwise, behaves as for ROUND_DOWN. This is the rounding mode that is typically taught in schools.
ROUND_HALF_DOWN
Round towards "nearest neighbor" unless both neighbors are equidistant, in which case round down. Behaves as for ROUND_UP if the discarded fraction is grater than 0.5; otherwise, behaves as for ROUND_DOWN.
ROUND_HALF_EVEN
Round towards the "nearest neighbor" unless both neighbors are equidistant, in which case, round towards the even neighbor. Behaves as for ROUND_HALF_UP if the digit to the left of the discarded fraction is odd; behaves as for ROUND_HALF_DOWN if it is even. This is the rounding mode that minimizes cumulative error when applied repeatedly over a sequence of calculations, and is sometimes referred to as Banker's rounding.
The following table gives a summary of rounding operations, with a precision of zero, under different rounding modes.
Input number ROUND UP ROUND DOWN ROUND CEILING ROUND FLOOR ROUND HALF UP ROUND HALF DOWN ROUND HALF EVEN
5.5 6 5 6 5 6 5 6
2.5 3 2 3 2 3 2 2
1.6 2 1 2 1 2 2 2
1.1 2 1 2 1 1 1 1
1.0 1 1 1 1 1 1 1
-1.0 -1 -1 -1 -1 -1 -1 -1
-1.1 -2 -1 -1 -2 -1 -1 -1
-1.6 -2 -1 -1 -2 -2 -2 -2
-2.5 -3 -2 -2 -3 -3 -2 -2
-5.5 -6 -5 -5 -6 -6 -5 -6
Examples using the default rounding mode (ROUND_HALF_EVEN):
ROUND(27.75, 2)
returns 27.75
ROUND(27.75, 1)
returns 27.8
ROUND(27.75, 0)
returns 28
ROUND(27.75, -1)
returns 30
Examples using a rounding mode with a precision of zero:
ROUND(5.5, 0 MODE ROUND_UP);
returns 6
ROUND(5.5, 0 MODE ROUND_DOWN);
returns 5
ROUND(5.5, 0 MODE ROUND_CEILING);
returns 6
ROUND(5.5, 0 MODE ROUND_FLOOR);
returns 5
ROUND(5.5, 0 MODE ROUND_HALF_UP);
returns 6
ROUND(5.5, 0 MODE ROUND_HALF_DOWN);
returns 5
ROUND(5.5, 0 MODE ROUND_HALF_EVEN);
returns 6
ROUND(2.5, 0 MODE ROUND_UP);
returns 3
ROUND(2.5, 0 MODE ROUND_DOWN);
returns 2
ROUND(2.5, 0 MODE ROUND_CEILING);
returns 3
ROUND(2.5, 0 MODE ROUND_FLOOR);
returns 2
ROUND(2.5, 0 MODE ROUND_HALF_UP);
returns 3
ROUND(2.5, 0 MODE ROUND_HALF_DOWN);
returns 2
ROUND(2.5, 0 MODE ROUND_HALF_EVEN);
returns 3

If possible, the scale is changed to the given value. If the result cannot be represented within the given scale, it is INFINITY.

Related concepts
ESQL overview
Related tasks
Developing ESQL
Related reference
Syntax diagrams: available types
ESQL numeric functions
Notices | Trademarks | Downloads | Library | Support | Feedback

Copyright IBM Corporation 1999, 2007Copyright IBM Corporation 1999, 2007. All Rights Reserved.
This build: July 31, 2007 21:32:01

ak05380_ This topic's URL is: