Skip to main content

PQL47 (PQL Function Library - CPM 4.7)

CASE_TABLE

Applies to: CELONIS 4.7

Description

CASE_TABLE allows to refer to the case table in the data model without using its exact table name. This operator is helpful in data models with multiple activity/case tables.

This function returns a reference to a case table. The result of this function can then be used at all places where table names are accepted.

Syntax
CASE_TABLE ( [ expression ] )

  • When an expression that involves a column of an activity table is passed as an argument, the case table connected to that activity table is referenced.

  • When no argument is passed, the default case table in the data model is referenced.

Examples

[1] In this example, the data model contains one activity and one case table. By using a column reference as the input expression and COUNT_TABLE operator, we count the total number of rows in the case table.

Query

Column1

COUNT_TABLE ( CASE_TABLE ( "Activities"."ACTIVITY" ) )

Input

Output

Activities

CASE_ID : STRING

ACTIVITY : STRING

TIMESTAMP : DATE

'1'

'A'

Wed Jan 01 2020 00:00:00.000

'1'

'B'

Thu Jan 02 2020 00:00:00.000

'2'

'A'

Wed Jan 01 2020 00:00:00.000

'2'

'C'

Thu Jan 02 2020 00:00:00.000

'3'

'D'

Wed Jan 01 2020 00:00:00.000

Cases

CASE_ID : STRING

NAME : STRING

'1'

'C1'

'2'

'C2'

'3'

'C3'

Foreign Keys

Cases.CASE_ID

Activities.CASE_ID

Result

Column1 : INT

3

[2] In this example, the data model contains one activity and one case table. By using a column reference as the input expression, we refer to the case table and query one of its columns.

Query

Column1

CASE_TABLE("Activities"."ACTIVITY")."NAME"

Input

Output

Activities

CASE_ID : STRING

ACTIVITY : STRING

TIMESTAMP : DATE

'1'

'A'

Wed Jan 01 2020 00:00:00.000

'1'

'B'

Thu Jan 02 2020 00:00:00.000

'2'

'A'

Wed Jan 01 2020 00:00:00.000

'2'

'C'

Thu Jan 02 2020 00:00:00.000

'3'

'D'

Wed Jan 01 2020 00:00:00.000

Cases

CASE_ID : STRING

NAME : STRING

'1'

'C1'

'2'

'C2'

'3'

'C3'

Foreign Keys

Cases.CASE_ID

Activities.CASE_ID

Result

Column1 : STRING

'C1'

'C2'

'C3'

[3] In this example, the data model contains one activity and one case table. By passing no arguments to the CASE_TABLE operator, we refer to the default case table. Then, the total number of rows of the case table is queried.

Query

Column1

COUNT_TABLE ( CASE_TABLE ( ) )

Input

Output

Activities

CASE_ID : STRING

ACTIVITY : STRING

TIMESTAMP : DATE

'1'

'A'

Wed Jan 01 2020 00:00:00.000

'1'

'B'

Thu Jan 02 2020 00:00:00.000

'2'

'A'

Wed Jan 01 2020 00:00:00.000

'2'

'C'

Thu Jan 02 2020 00:00:00.000

'3'

'D'

Wed Jan 01 2020 00:00:00.000

Cases

CASE_ID : STRING

NAME : STRING

'1'

'C1'

'2'

'C2'

'3'

'C3'

Foreign Keys

Cases.CASE_ID

Activities.CASE_ID

Result

Column1 : INT

3

[4] Here, CASE_TABLE is used inside a PU function. The "Activities"."ACTIVITY" column is aggregated to the corresponding case table.

Query

Column1

PU_COUNT ( CASE_TABLE ( "Activities"."ACTIVITY" ) , "Activities"."ACTIVITY" )

Input

Output

Activities

CASE_ID : STRING

ACTIVITY : STRING

TIMESTAMP : DATE

'1'

'A'

Wed Jan 01 2020 00:00:00.000

'1'

'B'

Thu Jan 02 2020 00:00:00.000

'2'

'A'

Wed Jan 01 2020 00:00:00.000

'2'

'C'

Thu Jan 02 2020 00:00:00.000

'3'

'D'

Wed Jan 01 2020 00:00:00.000

Cases

CASE_ID : STRING

NAME : STRING

'1'

'C1'

'2'

'C2'

'3'

'C3'

Foreign Keys

Cases.CASE_ID

Activities.CASE_ID

Result

Column1 : INT

2

2

1