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
conditionAny 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
| Operator | Example | Meaning |
|---|---|---|
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 FILTER | NOT(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
| Error | What happened | How to recover |
|---|---|---|
HTQL-1012 | A logical operator is not valid | Use AND, OR or NOT |
HTQL-1013 | The brackets or conditions are not structured correctly | Put each condition inside the brackets, separated by commas |
HTQL-8003 | A condition in a RETURN is a number or text, not a true or false value | Write a comparison, such as n > 0 |
HTQL-5001 | NOT in a RETURN was given more than one condition | Wrap 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)orNOT(0), answersHTQL-8003. NOTtakes one condition in a RETURN. For several, writeNOT(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.