Skip to main content

Celonis Product Documentation

MULTI_IN
Description

MULTI_IN returns true for all cases where one or multiple columns match any tuple of the match list. Returns false otherwise. The reverse is true for NOT MULTI_IN.

Syntax
 MULTI_IN ( (table.column, ...), (value1_1, ...), (value2_1, ...), ...)

 NOT MULTI_IN ( (table.column, ...), (value1_1, ...), (value2_1, ...), ...)
Supported types

Supported value types for the match list are STRING, INT, FLOAT, DATE.

Usage

MULTI_IN can be used in the following contexts:

Null handling

A match value can also be NULL. A NULL value matches with all cases of a column that are NULL.

Case sensitivity

Comparison of strings is case-sensitive.

Examples

[1]

Single column with tuples containing only one element. This is like the IN predicate.

Query

Column1 

         CASE WHEN MULTI_IN ( ( "Table1"."Column1" ) , ( 5 ) , ( 4 ) ) THEN 1 ELSE 0 END

Input

Output

Table1

Column1 : int

1

3

5

Result

Column1 : int

0

0

1

[2]

MULTI_IN lets you match against multiple columns.

Query

Column1 

         CASE WHEN MULTI_IN ( ( "Table1"."Column1" , "Table1"."Column2" ) , ( 5 , 6 ) , ( 1 , 2 ) ) THEN 1 ELSE 0 END

Input

Output

Table1

Column1 : int

Column2 : int

1

2

3

4

5

6

Result

Column1 : int

1

0

1

[3]

Column contains only integers and the tuples contain floats and integers. If a float and an integer represent the same value they match.

Query

Column1 

         CASE WHEN MULTI_IN ( ( "Table1"."Column1" , "Table1"."Column2" ) , ( 5.0 , 6 ) , ( 1 , 2 ) , ( 3.001 , 4 ) , ( 7.0 , 8.0 ) ) THEN 1 ELSE 0 END

Input

Output

Table1

Column1 : int

Column2 : int

1

2

3

4

5

6

7

8

Result

Column1 : int

1

0

1

1

[4]

MULTI_IN with dates and strings.

Query

Column1 

         CASE WHEN MULTI_IN ( ( "Table1"."Column1" , "Table1"."Column2" ) , ( {t 946771200000 } , 'AA' ) , ( {t 1009756800000 } , 'AA' ) , ( {t 1245145600000 } , 'AA' ) ) THEN "Table1"."Column1" ELSE null END

Input

Output

Table1

Column1 : date

Column2 : string

Sun Jan 02 2000 00:00:00.000

'AA'

Mon Dec 31 2001 00:00:00.000

'BB'

Sun Apr 16 2006 00:00:00.000

'CC'

Result

Column1 : date

Sun Jan 02 2000 00:00:00.000

null

null

[5]

NULL values in a column match against NULL values in the tuples.

Query

Column1 

         CASE WHEN MULTI_IN ( ( "Table1"."Column1" , "Table1"."Column2" ) , ( 1 , 'A' ) , ( 3 , NULL ) , ( 4 , 'D' ) , ( NULL , NULL ) ) THEN 1 ELSE 0 END

Input

Output

Table1

Column1 : int

Column2 : string

1

'A'

2

''

3

null

null

'D'

null

null

Result

Column1 : int

1

0

1

0

1

[6]

NULL values can also be used in the first tuple.

Query

Column1 

         CASE WHEN MULTI_IN ( ( "Table1"."Column1" , NULL ) , ( 1 , 2 ) , ( 3 , NULL ) , ( 7 , 8 ) ) THEN "Table1"."Column2" ELSE 0 END

Input

Output

Table1

Column1 : int

Column2 : int

1

2

3

4

5

6

7

8

Result

Column1 : int

0

4

0

0

[7]

Using MULTI_IN in a FILTER query:

Query

Filter 

         FILTER MULTI_IN ( ( "Table1"."Column1" , "Table1"."Column2" ) , ( NULL , 1 ) , ( '3' , 3 ) );

Column1 

         "Table1"."Column1"

Input

Output

Table1

Column1 : string

Column2 : int

null

1

''

2

'3'

3

'4'

4

Result

Column1 : string

null

'3'

[8]

NOT MULTI_IN used on INT in a CASE WHEN context.

Query

Column1 

         CASE WHEN NOT MULTI_IN ( ( "Table1"."Column1" , "Table1"."Column2" ) , ( 5.0 , 6 ) , ( 1 , 2 ) , ( 3.001 , 4 ) , ( 7.0 , 8.0 ) ) THEN 1 ELSE 0 END

Input

Output

Table1

Column1 : int

Column2 : int

1

2

3

4

5

6

7

8

Result

Column1 : int

0

1

0

0

See also: