Caché SQL Reference
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   

A numeric function that rounds or truncates a number at a specified number of digits.

{fn ROUND(numeric-expr,scale[,flag])}
numeric-expr The number to be rounded. A numeric expression.
scale An expression that evaluates to an integer that specifies the number of places to round to, counting from the decimal point. Can be zero, a positive integer, or a negative integer. If scale is a fractional number, Caché rounds it to the nearest integer.
flag Optional — A boolean flag that specifies whether to round or truncate the numeric-expr: 0=round, 1=truncate. The default is 0.
This function can be used to either round or truncate a number to the specified number of decimal digits.
ROUND rounds or truncates numeric-expr to scale places, counting from the decimal point. When rounding, the number 5 is always rounded up. Leading and trailing zeroes are removed before the ROUND operation. ROUND returns the same data type as numeric-expr.
Note that the ROUND return value is always normalized, removing trailing zeros. For example, ROUND(10.004,2) returns 10, not 10.00. When a fixed number of decimal digits is important — for example, when representing monetary amounts — the user must use the SQL scale parameter to reinstate the proper number of trailing zeros following the rounding operation.
ROUND and TRUNCATE perform similar operations; they both can be used to decrease the number of significant decimal or integer digits of a number. ROUND can be used to either round or truncate a number. TRUNCATE can only be used to truncate a number.
The following example uses a scale of 0 (zero) to round several fractions to integers. It shows that 5 is always rounded up:
SELECT ROUND(5.99,0) AS RoundUp,
       ROUND(5.5,0) AS Round5,
       {fn ROUND(5.329,0)} AS Roundoff
The following example truncates the same fractional numbers as the previous example:
SELECT ROUND(5.99,0,1) AS Trunc1,
       ROUND(5.5,0,1) AS Trunc2,
       {fn ROUND(5.329,0,1)} AS Trunc3
The following ROUND functions round and truncate a negative fractional number:
SELECT ROUND(-0.987,2,0) AS Round1,
       ROUND(-0.987,2,1) AS Trunc1
The following example rounds off pi to four decimal digits:
SELECT {fn PI()} AS ExactPi, ROUND({fn PI()},4) AS ApproxPi
The following example specifies a scale larger than the number of decimal digits:
SELECT {fn ROUND(654.98700,9)} AS Rounded
it returns 654.987 (Caché removed the trailing zeroes before the rounding operation; no rounding or zero padding occurred).
The following example rounds off the value of Salary to the nearest thousand dollars:
SELECT Salary,ROUND(Salary, -3) AS PayBracket
FROM Sample.Employee
Note that if Salary is less than five hundred dollars, it is rounded to 0 (zero).
In the following example each ROUND specifies a negative scale as large or larger than the number to be rounded:
SELECT {fn ROUND(987,-3)} AS Round1,
       {fn ROUND(487,-3)} AS Round2,
       {fn ROUND(987,-4)} AS Round3,
       {fn ROUND(987,-5)} AS Round4
The first ROUND function returns 1000, because the rounded result has more digits than the scale. The other three ROUND functions return 0 (zero).
See Also