calculatestatement is used to perform arithmetic operations using literal and column values.
source operation source
where source is either of
and operation is one of the characters
+ - * / %for addition, subtraction, multiplication, division and modulo respectively.
calculate column ResultCol as column Amount * value 1.2
calculate column Net as column total - column cogs
calculate column constant_7 as value 3.5 + value 3.5
The ResultCol parameter is the name of the column that will hold the results. This column may or may not exist (if necessary it will be created automatically).
Both of the two source parameters can specify a literal value, or the name of a column containing the value to use when performing the calculation.
- A literal value is specified using
Nwhere N is the literal number required
- A column name is specified using
colNamewhere ColName is the name of the column containing the values required
- Any blank or non-numeric values in a source column will be treated as 0
- An attempt to divide by zero will result in 0
- When performing a modulo operation, the two source values are rounded to the nearest integer first
- Add 1.5 to the values in the Rate column:
calculate column Rate as column Rate + value 1.5
- Multiply the values in the Rate column by those in the Quantity column
- Store the result in a new column called Charge
calculate column Charge as column Rate * column Quantity