Logical operators

Combine conditions into one answer.

Syntax

Each operator is written like a function, with its conditions inside brackets.

AND(condition, condition, …)
OR(condition, condition, …)
NOT(condition)

Parameters and arguments

  • condition

    Any comparison (including IN, LIKE, BETWEEN, RANGE and IS NULL), a true or false value such as a record's flag, or another AND, OR or NOT

    Required: At least one

Supported operators

OperatorExampleMeaning
AND(a, b, …)AND(financialStatus = "paid", total >= 100)Keeps the paid orders worth 100 or more; an order that fails either condition is left out
OR(a, b, …)OR(channel = "web", channel = "pos")Keeps the orders from either of two channels; one is enough
NOT(a)NOT(financialStatus = "refunded")Keeps every order that has not been refunded
NOT(a, b) in a FILTERNOT(financialStatus = "paid", total < 100)Leaves out only the small paid orders; a paid order worth 100 or more stays, and so does a small order that is not paid

In a RETURN, NOT takes exactly one condition.

Examples

Each operator on a record's fields, in one result.

Return type and behaviour

Each returns true or false. In a FILTER, the rows for which the result is true are kept. Each takes at least one condition.

Errors and exceptions
ErrorWhat happenedHow to recover
HTQL-1012A logical operator is not validUse AND, OR or NOT
HTQL-1013The brackets or conditions are not structured correctlyPut each condition inside the brackets, separated by commas
HTQL-8003A condition in a RETURN is a number or text, not a true or false valueWrite a comparison, such as n > 0
HTQL-5001NOT in a RETURN was given more than one conditionWrap them: NOT(AND(a, b))
Placement and context

Inside a FILTER, where they combine the conditions that keep a row, and in a RETURN or a LET, where they give a true or false value. They nest as deep as you need, and the commas between a FILTER's conditions are an AND.

Limitations
  • In a RETURN, give a condition a true or false value. A number or text as a condition, such as AND(true, 1) or NOT(0), answers HTQL-8003.
  • NOT takes one condition in a RETURN. For several, write NOT(AND(a, b)).
Best practices
  • Use OR for alternatives and AND for requirements.
  • Nest groups to make the intent clear, one group per line.
  • Name a rule once in a record and return it, rather than repeating it.