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

FunctionWhat 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

OperatorExampleMeaning
+o.subtotal + o.shippingThe goods and the shipping together, before tax
-o.subtotal - o.discountWhat the goods cost once the discount is taken off
*l.unitPrice * l.quantityThe value of an order line: the price of one unit times the units bought
/l.lineTotal / l.quantityWhat one unit cost on a line, worked out from the line total
^l.unitPrice * 1.05 ^ 3The price after three yearly increases of 5 percent: the power is worked out first, then it multiplies the price
( )(o.subtotal - o.discount) * 1.1Takes 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
ErrorWhat happenedHow to recover
HTQL-1003The expression is not written in HTQL's grammar, for example a computation on the left of a comparisonMove the computation to the right side, or into a LET
HTQL-8002DIVIDE was asked to divide by zeroCheck the divisor first
HTQL-1010A name was read as an arithmetic term and is not a numberUse 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. Use DIVIDE(a, b) where a zero divisor must be refused with HTQL-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 LET only when you read it more than once.
  • Work totals out in the statement, so the application receives the finished number.