Skip to main content

PQL47 (PQL Function Library - CPM 4.7)

MULTI_IN

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

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: