8.4.9 ROUND

(1) Specification format

scalar-function-ROUND ::= ROUND(target-data[,num-digits])
 
  target-data ::= value-expression
  num-digits ::= value-expression

To Page Top

(2) Explanation of specification format

target-data:

Specifies numeric data (the value to be rounded to the nth digit after the decimal point).

The following rules apply:

• Specify the target data in the form of a value expression. For details about value expressions, see 7.20 Value expression.

• Specify numeric data for the target data. For details about numeric data, see (1) Numeric data in 6.2.1 List of data types.

• You cannot specify a dynamic parameter by itself for the target data.

num-digits:

Specifies the number of digits.

The following rules apply:

• Specify num-digits in the form of a value expression. For details about value expressions, see 7.20 Value expression.

• Specify data of type INTEGER or SMALLINT for num-digits.

• If you omit num-digits, zero is assumed.

• If you specify a dynamic parameter for num-digits, the dynamic parameter will be assumed to be INTEGER type.

To Page Top

(3) Rules

1. The data type of the execution result is shown in the following table.

   Table 8‒6: Data type of the execution result of the scalar function ROUND

   Data type of the target data		Data type of the execution result
   INTEGER		INTEGER
   SMALLINT		SMALLINT
   DECIMAL(p,s)	when p ≤ 37	DECIMAL(p + 1,s)
   	when p = 38	DECIMAL(38,s)
   DOUBLE PRECISION		DOUBLE PRECISION

2. If the execution results cannot be represented in the data type of the target data, an overflow error is generated.

3. If the data type of the target data is SMALLINT, INTEGER, or DECIMAL, the function returns a value whose fractional part is rounded to n + 1 digits.

   Example: ROUND(325.72,1) → 325.70

   In the case of negative values, the target data is rounded as follows:

   Example 1: ROUND(-2.3,0) → -2.0

   Example 2: ROUND(-2.7,0) → -3.0

4. If the data type of the target data is DOUBLE PRECISION, midpoint values at position n + 1 are rounded to the nearest even number.

5. If you specify a negative value for num-digits, it rounds the integer part at the specified decimal place.

   Example: ROUND(325.72,-1) → 330.00

6. If you omit num-digits, or specify 0, all decimal places are rounded, and the execution result will be rounded to the integer part (to the ones position).

   Example: ROUND(325.72,0) → 326.00

7. If you specify a number of digits that is outside the range of the target data, it is handled as follows:

   • If you specified a positive value for num-digits, no rounding is performed. The value of the original target data is returned unchanged.

     Example 1: ROUND(0.12,5) → 0.12

     Example 2: ROUND(58,1) → 58

   • If you specified a negative value for num-digits, it returns 0.

     Example: ROUND(58,-5) → 0

8. The NOT NULL constraint does not apply to the value of the execution result (the null value is allowed).

9. If target-data or num-digits has a null value, the execution result will be a null value.

To Page Top

(4) Example

Example:

Round the values of columns C1 to C3 in table T1 as follows.

• Column C1: Round to the first decimal place (by rounding off the second decimal place).

• Column C2: Round to the hundreds column of the integer part (by rounding off the tens column).

• Column C3: Round to the first decimal place, rounding midpoint values to the nearest even number.

This assumes that HADB is running in an environment in which the rounding mode is round-to-even.

SELECT ROUND("C1",1),ROUND("C2",-2),ROUND("C3",0) FROM "T1"

To Page Top