Skip to main content

Celonis Product Documentation

CASE_TABLE
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

[5]

In this example, MERGE_EVENTLOG is used to combine two activity tables into one. We can use CASE_TABLE to get the corresponding case table of the merged activity table. In both columns of this example query, the same two activity tables are merged together using MERGE_EVENTLOG, and only the order of the input activity tables is different. As the corresponding case table of any MERGE_EVENTLOG result is defined as the case table of the first argument, CASE_TABLE of the first merged activity table refers to table BKPF (the case table of ACTIVITIES_BKPF), which contains one row. CASE_TABLE of the second merged activity table refers to table BSEG (the case table of ACTIVITIES_BSEG), which contains two rows:

Query

Column1 

         COUNT_TABLE ( CASE_TABLE ( MERGE_EVENTLOG ( "ACTIVITIES_BKPF"."ACTIVITY" , "ACTIVITIES_BSEG"."ACTIVITY" ) ) )

Column2 

         COUNT_TABLE ( CASE_TABLE ( MERGE_EVENTLOG ( "ACTIVITIES_BSEG"."ACTIVITY" , "ACTIVITIES_BKPF"."ACTIVITY" ) ) )

Input

Output

ACTIVITIES_BKPF

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

'C1'

'A'

Wed Jan 01 2020 03:00:00.000

ACTIVITIES_BSEG

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

'D1'

'X'

Wed Jan 01 2020 01:00:00.000

'D1'

'Y'

Thu Jan 02 2020 01:00:00.000

'D2'

'X'

Wed Jan 01 2020 02:00:00.000

'D2'

'Z'

Thu Jan 02 2020 02:00:00.000

BKPF

CASE_ID : string

'C1'

BSEG

CASE_ID : string

SUPER_CASE_ID : string

'D1'

'C1'

'D2'

'C1'

Foreign Keys

BKPF.CASE_ID

ACTIVITIES_BKPF.CASE_ID

BSEG.CASE_ID

ACTIVITIES_BSEG.CASE_ID

BKPF.CASE_ID

BSEG.SUPER_CASE_ID

Result

Column1 : int

Column2 : int

1

2