Skip to main content

PQL47 (PQL Function Library - CPM 4.7)

IN

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

Output

Table1

Column1 : INT

1

3

5

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

Output

Table1

Column1 : INT

1

3

5

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

Output

Table1

Column1 : INT

1

3

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

Output

Table1

Column1 : DATE

Sun Jan 02 2000 00:00:00.000

Mon Dec 31 2001 00:00:00.000

Mon Dec 31 2001 00:00:00.000

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

Output

Table1

Column1 : STRING

'1'

'3'

'0'

'@'

'aAa'

'bBb'

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

Output

Table1

Column1 : STRING

"

'2'

null

'3'

'4'

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

Output

Table1

Column1 : STRING

null

"

'3'

'4'

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

Output

Table1

Column1 : STRING

null

'1'

'2'

'3'

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

Output

InTable

Column1 : INT

1

2

null

Table1

Column1 : INT

2

3

4

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

Output

InTable

Column1 : INT

2

3

null

Table1

Column1 : INT

1

2

null

Result

Column1 : STRING

'no match'

'match'

'no match'

See also: