Logical Functions
Logical functions return value based on some logical conditions.
case when
case(when: condition_expression, then: value_expression, else: value_expression)
Description
The CASE statement goes through conditions and returns a value when the first condition is met (like an IF-THEN-ELSE statement).
Return type
Vary
Example
Given a Holistics expression as below:
case(
when: users.gender == 'm', then: 'male',
when: users.gender == 'f', then: 'female',
else: 'others'
)
The SQL output would be:
Case
When users.gender = 'm' then 'male'
When users.gender = 'f' then 'female'
Else 'others'
End
And the result would be:
| gender | case |
|---|---|
| m | male |
| f | female |
| m | male |
and()
and(condition_expression, ...)
Description
Logical AND compares between two Booleans as expression and returns true when both expressions are true.
Return type
Boolean
Example
Given a Holistics expression as below:
and(
products.id >= 2,
products.id <= 8
)
The SQL output would be:
(
(products.id >= 2.0) AND (products.id <= 8.0)
)
And the result would be:
| id | and |
|---|---|
| 1 | false |
| 2 | true |
| 8 | true |
| 9 | false |
or()
or(condition_expression, ...)
Description
Logical OR compares two Booleans as expression and returns true when one of the expressions is true.
Return type
Boolean
Example
Given a Holistics expression as below:
or(condition_expression, ...)
The SQL output would be:
or(
products.id <= 2,
products.id >= 8
)
And the result would be:
| id | or |
|---|---|
| 1 | true |
| 4 | false |
| 7 | false |
| 9 | true |
not()
not(field_expression)
Description
Logical NOT takes a single Boolean as an argument and invert it.
Return type
Boolean
Example
Given a Holistics expression as below:
not(is(products.id, null))
The SQL output would be:
NOT (products.id IS NULL)
And the result would be:
| id | not |
|---|---|
| 1 | true |
| false | |
| 3 | true |
| 4 | true |
is()
is(field_expression)
Description
Logical IS evaluates the given statement and return either true or false.
Return type
Boolean
Example
Given a Holistics expression as below:
is(products.id, null))
The SQL output would be:
(products.id IS NULL)
And the result would be:
| id | not |
|---|---|
| 1 | false |
| true | |
| 3 | false |
| 4 | false |
in()
in(field_expression, value_expression, value...)
Description
in operator takes a field expression and a list of values. Return true if that list of values contains the value of that field expression.
Return type
Boolean
Example
Given a Holistics expression as below:
in(users.name, 'bob', 'alice', 'jack)
The SQL output would be:
user.name in ('bob', 'alice', 'jack)
And the result would be:
| name | in |
|---|---|
| bob | true |
| alice | true |
| peter | false |