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:

Note that if the left value is larger than the right value, then an empty column will be returned.


[1] Empty INT interval in a FILTER context.

Query
Filter
FILTER "Table1"."Column1" BETWEEN 3 AND 1
Column1
"Table1"."Column2"
Input
Table1
Column1 : INTColumn2 : INT
1
11
2
12
3
13
4
14
5
15
Output
Result
Column1 : INT


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.


[2] Empty INT interval with initial NULL argument in a FILTER context.

Query
Filter
FILTER "Table1"."Column1" BETWEEN NULL AND 2
Column1
"Table1"."Column2"
Input
Table1
Column1 : INTColumn2 : INT
null
10
1
11
2
12
3
13
4
14
5
15
Output
Result
Column1 : INT


Case sensitivity

Comparison of strings is case-sensitive.

Examples


[3] BETWEEN used on INT in a CASE WHEN context.

Query
Column1
CASE WHEN "Table1"."Column1" BETWEEN 2 AND 4 THEN "Table1"."Column1" ELSE null END
Input
Table1
Column1 : INT
1
2
3
4
5
Output
Result
Column1 : INT
null
2
3
4
null



[4] BETWEEN <FLOAT> AND <FLOAT> used on FLOAT table in a FILTER context.

Query
Filter
FILTER "Table1"."Column1" BETWEEN 2.2 AND 4.4
Column1
"Table1"."Column2"
Input
Table1
Column1 : FLOATColumn2 : INT
1.1
11
2.2
12
3.3
13
4.4
14
5.5
15
Output
Result
Column1 : INT
12
13
14



[5] NOT BETWEEN used on INT in a PU_SUM context.

Query
Column1
"companyDetail"."companyCode"
Column2
PU_SUM("companyDetail", "caseTable"."value", "caseTable"."value" NOT BETWEEN 300 AND 400)
Input
caseTable
caseId : INTcompanyCode : STRINGvalue : INT
1
'001'
600
2
'001'
400
3
'001'
200
4
'002'
300
5
'002'
300
6
'003'
200
companyDetail
companyCode : STRINGcountry : STRING
'001''DE'
'002''DE'
'003''US'

Foreign Keys
caseTable.companyCodecompanyDetail.companyCode
Output
Result
Column1 : STRINGColumn2 : INT
'001'
800
'002'
null
'003'
200



[6] Closed interval on the same DATE argument in a CASE WHEN context.

Query
Column1
CASE WHEN "Table1"."Day" BETWEEN {d'2019-01-03'} AND {d'2019-01-03'} THEN "Table1"."Index" ELSE null END
Input
Table1
Index : INTDay : DATE
1
Tue Jan 01 2019 00:00:00.000
2
Wed Jan 02 2019 00:00:00.000
3
Thu Jan 03 2019 00:00:00.000
4
Fri Jan 04 2019 00:00:00.000
5
Sat Jan 05 2019 00:00:00.000
Output
Result
Column1 : INT
null
null
3
null
null



[7] BETWEEN used on STRING in a CASE WHEN context.

Query
Column1
CASE WHEN "Table1"."Char" BETWEEN 'C' AND 'c' THEN "Table1"."ASCII_value" ELSE null END
Input
Table1
ASCII_value : INTChar : STRING
65
'A'
66
'B'
67
'C'
68
'D'
97
'a'
98
'b'
99
'c'
100
'd'
Output
Result
Column1 : INT
null
null
67
68
97
98
99
null


  • No labels