Arithmetic
Compute a number where you need it.
Syntax
Write operands with an operator between them. Brackets group a part.
a + b a - b a * b a / b a ^ b
(a + b) * c
LET total = p.price * p.qty
RETURN {
"next": n + 1,
"ladder": [n, n * 2]
}
ADD(a, b) SUBTRACT(a, b) MULTIPLY(a, b) DIVIDE(a, b)Parameters and arguments
Each operator takes two operands. An operand is a whole or decimal number (negative allowed), a variable, a record's field such as p.price, a function's result such as ABS(x), or a bracketed group.
^Raise to a power
* /Multiply, divide
+ -Add, subtract
| Function | What it does |
|---|---|
ADD(a, b, …) | Adds every argument |
SUBTRACT(a, b) | Takes the second from the first |
MULTIPLY(a, b, …) | Multiplies every argument |
DIVIDE(a, b) | Divides the first by the second. Dividing by zero is HTQL-8002 |
A lone value such as 5, total or ABS(x) is a value, not a computation: the computation starts when an operator or an opening bracket follows.
Supported operators
| Operator | Example | Meaning |
|---|---|---|
+ | o.subtotal + o.shipping | The goods and the shipping together, before tax |
- | o.subtotal - o.discount | What the goods cost once the discount is taken off |
* | l.unitPrice * l.quantity | The value of an order line: the price of one unit times the units bought |
/ | l.lineTotal / l.quantity | What one unit cost on a line, worked out from the line total |
^ | l.unitPrice * 1.05 ^ 3 | The price after three yearly increases of 5 percent: the power is worked out first, then it multiplies the price |
( ) | (o.subtotal - o.discount) * 1.1 | Takes the discount off first and then adds ten percent for tax, so the discount is not taxed |
Precedence is the usual one: 1 + 2 * 3 is 7, (1 + 2) * 3 is 9, 10 - 4 / 2 is 8, and 2 * 3 ^ 2 is 18. ^ groups from the right, so 2 ^ 3 ^ 2 is 2 ^ 9, which is 512. A number can be negative: -2 * 3.
Examples
A sum of decimals, returned with its full value.
Return type and behaviour
Integers stay integers: 3 * 4 is 12, 40 + 2 is 42 and 2 ^ 10 is 1024. A division that does not come out even, or any operand that is a decimal, gives a decimal: 7 / 2 is 3.5.
A computed decimal follows floating point and the answer carries its full value. The screen that shows it decides how many places to display; money held in a table is exact and arrives as an amount.
Errors and exceptions
| Error | What happened | How to recover |
|---|---|---|
HTQL-1003 | The expression is not written in HTQL's grammar, for example a computation on the left of a comparison | Move the computation to the right side, or into a LET |
HTQL-8002 | DIVIDE was asked to divide by zero | Check the divisor first |
HTQL-1010 | A name was read as an arithmetic term and is not a number | Use a number, or return the variable as it is |
Limitations
- Not supported: a computation on the left of a comparison, such as
qty * 2 > 5. Write the computation on the right,5 < qty * 2, or compute it in a variable and compare the variable. - Not supported:
%. For a remainder, use the MOD function. /is a plain division. UseDIVIDE(a, b)where a zero divisor must be refused withHTQL-8002.
Best practices
- Rely on the usual precedence, and add brackets only where they make the intent clear.
- Return the full value and let the screen that shows it format it. HTQLDB converts money and percentages in the answer, so a statement never rounds or scales a result for display.
- Compute a value once in a
LETonly when you read it more than once. - Work totals out in the statement, so the application receives the finished number.