The round
statement is used to ensure that numeric values in a column are whole multiples of a specified number.
round
colName [direction]
[to nearest
value]
The round
statement is used to round numbers to the nearest multiple of any integer or floating point number.
The parameters supported by round
are as follows:
Parameter | Default | Purpose |
colName | n/a | The name of the column containing the values to round |
direction |
| Whether to round to the next highest ( |
value |
| A value determining the granularity of the rounding |
The simplest form of the statement is round colName
. This will use the defaults shown in the table above such that values are rounded to the next highest integer. Alternatively, the statement round colName down
will round down to the nearest integer.
If the value argument is provided then the numbers in colName will be rounded up or down to the nearest multiple of value. For example the statement ...
round Quantity up to nearest 5
... will round the numbers in the column Quantity up to the next multiple of 5. Thus any number in Quantity higher than 10 and less than 15 will be rounded to 15.
When specifying the value argument, floating point values are supported.
The round
statement observes the following rules:
Non-numeric values in colName are ignored
Blank values, or a value of 0
in colName are ignored
Numbers in colName that are already a whole multiple of value will not be modified
The round
statement may be used in the body of a where statement to perform conditional rounding.
round Quantity # Round up to nearest integerround Quantity down # Round down to nearest integerround Quantity up to nearest 4 # Round up to next multiple of 4round Quantity down to nearest 2 # Round down to next lowest even numberâ€‹round Quantity up to nearest 0.5 # ie: 2.25 -> 2.5round Quantity down to nearest 0.1 # ie: 23.34567 -> 23.3round Quantity down to nearest 0.01 # ie: 23.34567 -> 23.34â€‹where ( ([quantity] > 5) && ([quantity] <= 100) ) {round quantity up to nearest 10 # Force consumption in blocks of 10}â€‹where ([quantity] > 100) {round quantity up to nearest 5 # Better deals for larger consumption}