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

Description

IN returns true for all cases of a column that match any of the values of the match list. An item in the match list can also be another column (Since:  CELONIS 4.7). Returns false otherwise. The reverse is true for NOT IN.

Syntax

table.column IN ( value1/column1, ... )
table.column NOT IN ( value1/column1, ... )

Supported types

Supported value types for the match list are STRING, INT, FLOAT, DATE, and any column of that type.

Usage

[NOT] IN can be used in the following contexts:

NULL handling

A match value can also be NULL. A NULL value matches with all NULL values in the match list. NULL values originating from columns in the match list are ignored.

Case sensitivity

Comparison of strings is case-sensitive

Examples


[1] Column and match list contain only integers.

Query
Column1
CASE WHEN "Table1"."Column1" IN ( 1 , 3 ) THEN "Table1"."Column1" ELSE null END
Input
Table1
Column1 : INT
1
3
5
Output
Result
Column1 : INT
1
3
null



[2] NOT IN: Results are negated. Column and match list contain only integers.

Query
Column1
CASE WHEN "Table1"."Column1" NOT IN ( 1 , 3 ) THEN "Table1"."Column1" ELSE null END
Input
Table1
Column1 : INT
1
3
5
Output
Result
Column1 : INT
null
null
5



[3] Column contains only integers and match list contains floats and integers. All cases of the column are matched.

Query
Column1
CASE WHEN "Table1"."Column1" IN ( 1.0 , 3 ) THEN "Table1"."Column1" ELSE null END
Input
Table1
Column1 : INT
1
3
Output
Result
Column1 : INT
1
3



[4] Column and match list contain only dates.

Query
Column1
CASE
    WHEN
        "Table1"."Column1" IN ( {t 946771200000 } , {t 1009756800000 } , {t 1245145600000 } )
    THEN
        "Table1"."Column1"
    ELSE
        null
END
Input
Table1
Column1 : DATE
Sun Jan 02 2000 00:00:00.000
Mon Dec 31 2001 00:00:00.000
Sun Apr 16 2006 00:00:00.000
Output
Result
Column1 : DATE
Sun Jan 02 2000 00:00:00.000
Mon Dec 31 2001 00:00:00.000
null



[5] Column and match list contain only strings and match list also contains empty string. Two cases of the column are matched.

Query
Column1
CASE
    WHEN
        ( "Table1"."Column1" IN ( '1' , '3' , '-10' , '' , 'aAa' , 'bbb' ) )
    THEN
        "Table1"."Column1"
    ELSE
        null
END
Input
Table1
Column1 : STRING
'1'
'3'
'0'
'@'
'aAa'
'bBb'
Output
Result
Column1 : STRING
'1'
'3'
null
null
'aAa'
null



[6] Column and match list contain empty string. Empty string is matched.

Query
Column1
CASE WHEN ( "Table1"."Column1" IN ( '1' , '3' , '-10' , '' ) ) THEN "Table1"."Column1" ELSE null END
Input
Table1
Column1 : STRING
''
'2'
null
'3'
'4'
Output
Result
Column1 : STRING
''
null
null
'3'
null



[7] Column and match list contains a NULL value and strings. The IN operator is used for column filtering.

Query
Filter
FILTER "Table1"."Column1" IN ( NULL , '3' );
Column1
"Table1"."Column1"
Input
Table1
Column1 : STRING
null
''
'3'
'4'
Output
Result
Column1 : STRING
null
'3'



[8] Column contains a NULL value and 3 strings and match list 3 integers. The IN operator is applied on the COUNT aggregation of the column. One value of the match list is matched.

Query
Column1
CASE WHEN COUNT ( "Table1"."Column1" ) IN ( 1 , 2 , 3 ) THEN 1 ELSE 0 END
Input
Table1
Column1 : STRING
null
'1'
'2'
'3'
Output
Result
Column1 : INT
1



[9] IN with another column in the match list.

Query
Column1
CASE WHEN "Table1"."Column1" IN ( "InTable"."Column1" ) THEN 1 ELSE 0 END
Input
InTable
Column1 : INT
1
2
null
Table1
Column1 : INT
2
3
4
Output
Result
Column1 : INT
1
0
0



[10] IN with another column containing NULL in the match list (in CASE WHEN statement).

Query
Column1
CASE WHEN "Table1"."Column1" IN ( "InTable"."Column1" ) THEN 'match' ELSE 'no match' END
Input
InTable
Column1 : INT
2
3
null
Table1
Column1 : INT
1
2
null
Output
Result
Column1 : STRING
'no match'
'match'
'no match'


See also:

  • No labels