Applies to:  CELONIS 4.7 

Description

MULTI_IN returns true for all cases one or multiple columns that 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
Table1
Column1 : INT
1
3
5
Output
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
Table1
Column1 : INTColumn2 : INT
1
2
3
4
5
6
Output
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
Table1
Column1 : INTColumn2 : INT
1
2
3
4
5
6
7
8
Output
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
Table1
Column1 : DATEColumn2 : 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'
Output
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
Table1
Column1 : INTColumn2 : STRING
1
'A'
2
''
3
null
null
'D'
null
null
Output
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
Table1
Column1 : INTColumn2 : INT
1
2
3
4
5
6
7
8
Output
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
Table1
Column1 : STRINGColumn2 : INT
null
1
''
2
'3'
3
'4'
4
Output
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
Table1
Column1 : INTColumn2 : INT
1
2
3
4
5
6
7
8
Output
Result
Column1 : INT
0
1
0
0


See also:

  • No labels