Share this page

Arithmetic Operators

Arithmetic operators are used to perform calculations on numeric data in a field or fields. They may be used alone or in combination with other operators and functions in both target field expressions.

• Numeric operands are promoted to double precision values before performing the operation.

• If both operands are integer values and the result can be represented as an integer, the operator returns an integer value.

• If one or both operands are Null, then the result is Null. All other values are treated as a numeric value of zero.

Arithmetic Operator Symbol | Name |

+ | addition |

- | subtraction |

* | multiplication |

/ | division |

\ | division of integers |

^ | exponentiation |

mod | mod |

& string concatenation | concatenate |

It is important to understand that calculations cannot be performed on data that contains non-numeric characters, or is defined as Text data type. However, there are functions available that allow you to convert data from Text to numeric values. See Val Function and ValMask Function.

Task | Description | Expression |

Multiply the contents of two fields from the source file | Source field names are Quantity and Price. Target field name is TOTAL SALE. | TOTAL SALE=FieldAt("/SOURCE/R1/Quantity")*("Price") |

Multiply the contents of one field from the source file by a literal value (add 7.25% sales tax) | Source field name is Total. Target field name is GRAND TOTAL. | GRAND TOTAL= FieldAt("/SOURCE/R1/Total") * 1.0725 |

Divide the contents of one field by the contents of another field from the source file | Source field names are Total and Quantity. Target field name is PRICE PER. | PRICE PER= FieldAt("/SOURCE/R1/Total Sale") / FieldAt("/SOURCE/R1/Quantity") |

Add the contents of two fields from the source file | Source field names are Total and Tax. Target field name is GRAND TOTAL. | GRAND TOTAL= FieldAt("/SOURCE/R1/Total") + FieldAt("/SOURCE/R1/Tax") |

Multiply (*) Operator

Description | Multiply two numeric operands |

Syntax | result = operand1 * operand2 |

Remarks | Numeric operands are promoted to double precision values before being multiplied. If both operands are integer values and the result can be represented as an integer, then the operator returns an integer value. If one or both operands are Null, then the result is Null. All other values are treated as a numeric value of zero. |

Example | The following expression returns the value of the contents of the field Salary multiplied by the constant 2. FieldAt("/SOURCE/R1/Salary") * 2 This expression returns the value of the contents of the Price Per field multiplied by the contents of the Quantity field: FieldAt("/SOURCE/R1/Price Per") * FieldAt("/SOURCE/R1/Quantity") |

Add (+) Operator

Description | Add two numeric operands or concatenates two string operands |

Syntax | result = operand1 + operand2 |

Remarks | The + operator treats operands as numbers (unless both are dates, and one contains only time information). So if either or both is a string, it is treated as a number. For string concatenation, the & operator is preferred to the + operator because there is less ambiguity. Numeric operands are promoted to double precision values before being added. If both operands are integer values and the result can be represented as an integer, then the operator returns an integer value. If one or both operands are Null, then the result is Null. |

Example | The following expression adds the contents of two numeric fields, Bonus and Salary: FieldAt("/SOURCE/R1/Bonus") + FieldAt("/SOURCE/R1/Salary") The following expression adds a constant value to the value in an existing field. FieldAt("/SOURCE/R1/Field10") + 100 For more information, see Arithmetic Operators. |

Difference (-) Operator

Description | Find the difference between two numeric operands or change the sign of a numeric operand |

Syntax | Syntax 1: result = operand1 - operand2 Syntax 2: result = - operand |

Remarks | The first form of the operator (Syntax 1) calculates the difference between two numeric values. It is the subtraction operator. In the second form (Syntax 2), the operator is arithmetic negation operator. Numeric operands are promoted to double precision values before performing the operation. If both operands are integer values and the result can be represented as an integer, then the operator returns an integer value. If one or both operands are Null, then the result is Null. All other values are treated as a numeric value of zero. |

Example | The following expression calculates the difference between two numeric fields List Price and Price: FieldAt("/SOURCE/R1/List Price") - FieldAt("/SOURCE/R1/Price" The following expression calculates the difference between a numeric field and a constant. FieldAt("/SOURCE/R1/List Price") - 20 |

Integer Division (\) Operator

Description | Integer division operator. Divide two numbers and return the integer result. |

Syntax | result = operand1 \ operand2 |

Remarks | If one or both operands are Null, then the result is Null. All other nonnumeric values (empty strings, strings composed of letters) are treated as a numeric value of zero |

Example | The following example determines if the field Quantity contains an even number (FieldAt("/SOURCE/R1/Quantity") * (FieldAt("/SOURCE/R1/Quantity")\2)) |

Numeric Division (/) Operator

Description | Division operator. Divide two numbers and return a result. |

Syntax | result = operand1 / operand2 |

Remarks | Numeric operands are promoted to double precision values before performing the operation. If both operands are integer values and the result can be represented as an integer, then the operator returns an integer value. If one or both operands are Null, then the result is Null. All other nonnumeric values (empty strings, strings composed of letters) are treated as a numeric value of zero. |

Example | The following example calculates the average monthly sales by dividing the contents of the Annual Sales field by the constant value of 12: FieldAt("/SOURCE/R1/Annual Sales") / 12 The following expression divides the numeric contents of one field by the numeric contents of another field: FieldAt("/SOURCE/R1/Field10") / FieldAt("/SOURCE/R1/Field23") |

Exponent (^) Operator

Description | Raise a number to the power of an exponent |

Syntax | result = number ^ exponent |

Remarks | Numeric operands are promoted to double precision values before performing the operation. If both operands are integer values and the result can be represented as an integer, then the operator returns an integer value. If one or both operands are Null, then the result is Null. All other values are treated as a numeric value of zero. |

Example | The following expression returns the value of the field Distance squared: FieldAt("/SOURCE/R1/Distance")^2 |

Mod Operator

Description | Divide two numbers and returns only the remainder |

Syntax | result = operand1 Mod operand2 |

Remarks | Mod is a shortened term for modulus. Mod divides operand1 by operand2 (rounding floating-point numbers to integers) and returns only the remainder as result. The operands can be any numeric expression. The data type of result is usually an Integer or a Long. However, result is a Null if one or both of the operands are Null expressions. Any operand that is Empty is treated as 0 (zero). |

Example operators, arithmetic | This example divides 200000 by 9 and determines the remainder. It returns 2. 200000 Mod 9 Mod is calculated as follows: I - n * (I \ n) Where: • I is a counter • n is the field count • "\" divides integers |

Operator | Calculation | Result |

1 Mod 12 | 1 - 12 * (1 \ 12) | 1 |

2 Mod 12 | 2 - 12 * (2 \ 12) | 2 |

3 Mod 12 | 3 - 12 * (3 \ 12) | 3 |

4 Mod 12 | 4 - 12 * (4 \ 12) | 4 |

5 Mod 12 | 5 - 12 * (5 \ 12) | 5 |

6 Mod 12 | 6 - 12 * (6 \ 12) | 6 |

7 Mod 12 | 7 - 12 * (7 \ 12) | 7 |

8 Mod 12 | 8 - 12 * (8 \ 12) | 8 |

9 Mod 12 | 9 - 12 * (9 \ 12) | 9 |

10 Mod 12 | 10 - 12 * (10 \ 12) | 10 |

11 Mod 12 | 11 - 12 * (11 \ 12) | 11 |

12 Mod 12 | 12 - 12 * (12 \ 12) | 0 |

Tip... Test your Mod operator calculations before running a transformation by opening the Windows Calculator and selecting Scientific mode. Using the examples above, click on "1", then "Mod", then "12". The result is "1".