Previous
Previous
 
Next
Next


ROUND (number)

When a number is specified as an argument, the ROUND function returns the number rounded to the nearest multiple of a second number you specify or to the number of decimal places indicated by the second number.

Return Value

DECIMAL (when the round type is MULTIPLE)

NUMBER (when the round type is DECIMAL)

Syntax

ROUND(number_exp roundvalue) [MULTIPLE|DECIMAL]

Parameters

number_exp

An expression that identifies the number to round.

roundvalue

A value that specifies the basis for rounding.

When the round type is MULTIPLE:

 

When the round type is DECIMAL:

MULTIPLE

(Default) Specifies that rounding is performed by rounding to the nearest multiple of roundvalue.

DECIMAL

Specifies that rounding is performed by rounding to the number of decimal places indicated by roundvalue.

Usage Notes

Using ROUND to Compare Expressions

A DECIMAL value might be stored in a slightly different form than shows up at the level of significant digits you are using. This small difference can cause unexpected results when you are comparing two expressions. The problem can occur even when you are comparing INTEGER expressions that involve calculations because many calculations are done only after converting INTEGER values to DECIMAL values. You do not generally see the difference in reports because reports usually show only two or three decimal places.

For example, when you compare two numbers with the EQ or NE operators, you probably want to ignore any difference caused by the least significant digits. When expense was stored as 100.00000001, the least significant digit would not be ignored by the simple form of the comparison.

The statement

SHOW expense EQ 100.00

produces the following result.

NO

However, you can use ROUND to force EQ or NE to ignore the least significant digits.

SHOW ROUND(expense, .01) EQ 100.00

This statement produces the following result.

YES

Using ABS to Compare Expressions

When speed of calculation is important in your application, you may want to use the ABS function with LT to compare numbers, instead of using ROUND with EQ or NE.

Examples

Rounding to Different Multiples

The following statements show the results of rounding the expression 2/3 to different multiples. The value of the DECIMALS setting is 2.

The statement

SHOW ROUND(2/3, .01)

produces the following result.

0.67

The statement

SHOW ROUND(2/3, .1)

produces the following result.

0.70

The statement

SHOW ROUND(2/3, .5)

produces the following result.

0.50

Rounding to the Nearest Thousand

The following example shows sales rounded to the nearest thousand.

LIMIT month TO FIRST 4
LIMIT district TO FIRST 1
REPORT ROUND(sales 1000)

These statements produce the following output.

DISTRICT: BOSTON
               -------------ROUND(SALES 1000)-------------
               -------------------MONTH-------------------
PRODUCT          Jan95      Feb95      Mar95      Apr95
-------------- ---------- ---------- ---------- ----------
Tents           32,000.00  33,000.00  43,000.00  58,000.00
Canoes          66,000.00  76,000.00  92,000.00 126,000.00
Racquets        52,000.00  57,000.00  59,000.00  69,000.00
Sportswear      53,000.00  59,000.00  63,000.00  68,000.00
Footwear        91,000.00  87,000.00 100,000.00 108,000.00

Rounding to the Nearest Multiple of 12

To show units rounded to the nearest multiple of 12, use the following statements.

LIMIT month TO FIRST 4
LIMIT district TO FIRST 1
REPORT DECIMAL 0 ROUND(units 12)

These statements produce the following output.

DISTRICT: BOSTON
               --------------ROUND(UNITS 12)--------------
               -------------------MONTH-------------------
PRODUCT          Jan95      Feb95      Mar95      Apr95
-------------- ---------- ---------- ---------- ----------
Tents                 204        204        264        360
Canoes                348        396        480        660
Racquets              996      1,080      1,116      1,308
Sportswear          1,092      1,212      1,296      1,404
Footwear            2,532      2,400      2,772      2,976

Rounding to Decimal Places

The following statements show the results of rounding 15.193 to various decimal places.

The statement

ROUND(15.193, 1)

produces the following result

15.2

The statement

ROUND(15.193, -1)

produces the following result

20