Skip to main content

PQL47 (PQL Function Library - CPM 4.7)

BETWEEN

Applies to: CELONIS 4.0 CELONIS 4.2 CELONIS 4.3 CELONIS 4.4 CELONIS 4.5 CELONIS 4.6 CELONIS 4.7

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, 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

Output

Table1

Column1 : INT

Column2 : INT

1

11

2

12

3

13

4

14

5

15

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

Output

Table1

Column1 : INT

Column2 : INT

null

10

1

11

2

12

3

13

4

14

5

15

 

Result

Column1 : INT

Note

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

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

Output

Table1

Column1 : INT

1

2

3

4

5

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

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

[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

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

[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

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

[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

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

See also: