Skip to main content

Celonis Product Documentation

COMMON_TABLE
Description

COMMON_TABLE allows to refer to the common table of multiple expressions in the data model without using its exact table name. This operator might be helpful when the common table of multiple expressions is unknown. The given input expressions have to be columns.

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

Syntax
 COMMON_TABLE ( expression [, expression, ...] )

  • The common table that serves as a common table for all the given input expressions is referenced

  • Only expressions that reference a column are supported as input

Examples

[1]

The common table of a single data model column is the owning table of this column.

Query

Column1 

         COMMON_TABLE("Cases"."NAME")."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'

[2]

The common table of two directly connected tables is the table on the N-side. Read more about common tables here.

Query

Column1 

         CASE WHEN BIND_FILTERS ( COMMON_TABLE ( "Cases"."NAME" , "Activities"."ACTIVITY" ) , "Cases"."NAME" = 'C1' ) THEN 'pass' ELSE NULL END

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

'pass'

'pass'

null

null

null

[3]

The common table of a generated column is the generated table owning this column.

Query

Column1 

         COMMON_TABLE(MERGE_EVENTLOG("ACTIVITIES_BKPF"."ACTIVITY","ACTIVITIES_BSEG"."ACTIVITY"))."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 : string

'X'

'X'

'A'

'Y'

'Z'