Skip to main content

Celonis Product Documentation

BETWEEN
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, INT, 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, BETWEEN evaluates to false.

NULL handling

If one of the input values (the input column or the interval start/end) is NULL, then the output will be NULL as well.

Case sensitivity

Comparison of strings is case-sensitive.

Examples

[1]

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

Output

Table1

Column1 : int

1

2

3

4

5

Result

Column1 : int

null

2

3

4

null

[2]

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

Output

Table1

Column1 : float

Column2 : int

1.1

11

2.2

12

3.3

13

4.4

14

5.5

15

Result

Column1 : int

12

13

14

[3]

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

Output

caseTable

caseId : int

companyCode : string

value : int

1

'001'

600

2

'001'

400

3

'001'

200

4

'002'

300

5

'002'

300

6

'003'

200

companyDetail

companyCode : string

country : string

'001'

'DE'

'002'

'DE'

'003'

'US'

Foreign Keys

caseTable.companyCode

companyDetail.companyCode

Result

Column1 : string

Column2 : int

'001'

800

'002'

null

'003'

200

[4]

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

Output

Table1

Index : int

Day : 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

Result

Column1 : int

null

null

3

null

null

[5]

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

Output

Table1

ASCII_value : int

Char : string

65

'A'

66

'B'

67

'C'

68

'D'

97

'a'

98

'b'

99

'c'

100

'd'

Result

Column1 : int

null

null

67

68

97

98

99

null

[6]

Empty INT interval in a FILTER context.

Query

Filter 

         FILTER "Table1"."Column1" BETWEEN 3 AND 1;

Column1 

         "Table1"."Column2"

Input

Output

Table1

Column1 : int

Column2 : int

1

11

2

12

3

13

4

14

5

15

(empty table)

[7]

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

Query

Filter 

         FILTER "Table1"."Column1" BETWEEN NULL AND 2;

Column1 

         "Table1"."Column2"

Input

Output

Table1

Column1 : int

Column2 : int

null

10

1

11

2

12

3

13

4

14

5

15

(empty table)

Warning

BETWEEN: Comparison with NULL always returns NULL. To check for NULL values, please use <value> IS NULL or ISNULL(<value>)=1.

See also: