Applies to: CELONIS 4.0 CELONIS 4.2 CELONIS 4.3 CELONIS 4.4 CELONIS 4.5 CELONIS 4.6
Description
BETWEEN ... AND ...
returns true for all cases of a column that are in the closed
interval given. Returns false otherwise. The reverse is true for NOT BETWEEN ... AND ...
.
Syntax
table.column BETWEEN inclusive start of interval AND inclusive end of interval
table.column NOT BETWEEN inclusive start of interval AND inclusive end of interval
Supported types
Supported value types for the interval start and end points are STRING, INTEGER, FLOAT and DATE. The interval start and end points must be of the same type.
[NOT] BETWEEN ... AND ...
can be used in the following contexts:
- FILTER (Since: CELONIS 4.0)
- CASE WHEN (Since: CELONIS 4.6)
- Pull-Up-Functions (Since: CELONIS 4.6)
Note that if the left value is larger than the right value, then an empty column will be returned.
Null handling
NULL values in the input column will evaluate to false in the output. If the interval start and/or end points are NULL, then an empty column will be returned.
Case sensitivity
Comparison of strings is case-sensitive.
Examples
Query
Column1
"companyDetail"."companyCode"
Column2
PU_SUM("companyDetail", "caseTable"."value", "caseTable"."value" NOT BETWEEN 300 AND 400)
Query
Column1
CASE WHEN "Table1"."Day" BETWEEN {d'2019-01-03'} AND {d'2019-01-03'} THEN "Table1"."Index" ELSE null END