Skip to main content

Celonis Product Documentation

UNION_ALL_TABLE
Description

The UNION_ALL_TABLE function returns the table formed from the vertical concatenation of the columns of the input tables.

SYNTAX
 UNION_ALL_TABLE ( table1 , ... , tableN )

UNION_ALL_TABLE requires at least 2 and at most 16 arguments.

Available columns in UNION_ALL_TABLE

UNION_ALL_TABLE will return the resulting table of the vertical concatenation of all columns that have the same name and the same data type in all input tables. If some input tables have a column with the same name and data type and some input tables do not contain such a column, null values will be returned for the values from the tables that do not contain the column. Whenever any two input tables contain a column with the same name but different data types, this column will not be available.

NULL handling

NULL values are preserved.

Limitations

The current limitations documented in Engine Limitations also hold for the result of UNION_ALL_TABLE. This means that the hard upper limit of rows for the result column of UNION_ALL_TABLE is 2.1 billion rows, and the recommended limit of rows is 800 million rows.

The input tables may not be temporary, i.e., constants and GLOBAL are not allowed.

Nesting of UNION_ALL_TABLE

UNION_ALL_TABLE cannot be nested.

Examples

[1]

Column access of the concatenation of two tables

Query

Column1 

         UNION_ALL_TABLE("caseTable","activityTable")."caseId"

Input

Output

City

city : string

size : int

'Frankfurt'

3500000

'Boston'

600000

'Lyon'

2200000

'Seattle'

640000

Country

country : string

capitol : string

'DE'

'Berlin'

'US'

'Washington'

'FR'

'Paris'

OrderPos

orderId : string

pos : int

country : string

city : string

'001'

1

'DE'

'Frankfurt'

'001'

2

'US'

'Boston'

'001'

3

'FR'

'Lyon'

'002'

1

'US'

'Seattle'

activityTable

activity : string

caseId : int

'A'

1

'B'

1

'C'

2

caseTable

caseId : int

orderId : string

1

'001'

2

'002'

Foreign Keys

caseTable.caseId

activityTable.caseId

caseTable.orderId

OrderPos.orderId

OrderPos.country

Country.country

OrderPos.city

City.city

Result

Column1 : int

1

2

1

1

2

[2]

Concatenation of mismatching data types between equal columns of the input tables is not allowed

Query

Column1 

         UNION_ALL_TABLE("Country","City")."country"

Input

Output

City

city : string

country : int

size : int

'Frankfurt'

49

3500000

'Boston'

350

600000

'Lyon'

47

2200000

'Seattle'

350

640000

Country

country : string

capitol : string

'DE'

'Berlin'

'US'

'Washington'

'FR'

'Paris'

Warning

UNION_ALL_TABLE: Tables have mismatching data types [STRING,INT] for column [country], column cannot be created for the union all table.

[3]

Registering the concatenation of two tables and accessing a column of the result

Query

         REGISTER "unionTable" AS UNION_ALL_TABLE ( "caseTable" , "activityTable" );

Column1 

         "unionTable"."caseId"

Input

Output

City

city : string

size : int

'Frankfurt'

3500000

'Boston'

600000

'Lyon'

2200000

'Seattle'

640000

Country

country : string

capitol : string

'DE'

'Berlin'

'US'

'Washington'

'FR'

'Paris'

OrderPos

orderId : string

pos : int

country : string

city : string

'001'

1

'DE'

'Frankfurt'

'001'

2

'US'

'Boston'

'001'

3

'FR'

'Lyon'

'002'

1

'US'

'Seattle'

activityTable

activity : string

caseId : int

'A'

1

'B'

1

'C'

2

caseTable

caseId : int

orderId : string

1

'001'

2

'002'

Foreign Keys

caseTable.caseId

activityTable.caseId

caseTable.orderId

OrderPos.orderId

OrderPos.country

Country.country

OrderPos.city

City.city

Result

Column1 : int

1

2

1

1

2

[4]

Extending the resulting concatenation of the two tables

Query

         REGISTER "unionTable" AS UNION_ALL_TABLE ( "caseTable" , "activityTable" );
        
         EXTEND "unionTable" WITH "orderId" AS UNION_ALL ( "caseTable"."orderId" , BIND ( "activityTable" , NULL ) );

Column1 

         "unionTable"."caseId"

Column2 

         "unionTable"."orderId"

Input

Output

City

city : string

size : int

'Frankfurt'

3500000

'Boston'

600000

'Lyon'

2200000

'Seattle'

640000

Country

country : string

capitol : string

'DE'

'Berlin'

'US'

'Washington'

'FR'

'Paris'

OrderPos

orderId : string

pos : int

country : string

city : string

'001'

1

'DE'

'Frankfurt'

'001'

2

'US'

'Boston'

'001'

3

'FR'

'Lyon'

'002'

1

'US'

'Seattle'

activityTable

activity : string

caseId : int

'A'

1

'B'

1

'C'

2

caseTable

caseId : int

orderId : string

1

'001'

2

'002'

Foreign Keys

caseTable.caseId

activityTable.caseId

caseTable.orderId

OrderPos.orderId

OrderPos.country

Country.country

OrderPos.city

City.city

Result

Column1 : int

Column2 : string

1

'001'

2

'002'

1

null

1

null

2

null

[5]

UNION_ALL_TABLE can take the results of CREATE_EVENTLOG as arguments

Query

         REGISTER "unionTable" AS UNION_ALL_TABLE ( CREATE_EVENTLOG ( "o_celonis_SalesOrderItem" , INCLUDE [ "e_celonis_CreateDeliveryItem" ] ) , CREATE_EVENTLOG ( o_celonis_DeliveryItem ) );

Column1 

         "unionTable"."LEAD_OBJECT_ID"

Input

Output

e_celonis_AddSalesOrderItem

ID : string

TIME : date

SalesOrderItem_ID : string

'E010'

Thu Feb 02 2023 00:00:00.000

'O10'

'E011'

Fri Mar 03 2023 00:00:00.000

'O11'

'E012'

Sat Mar 04 2023 00:00:00.000

'O12'

'E013'

Sun Mar 05 2023 00:00:00.000

'O13'

'E014'

Mon Mar 06 2023 00:00:00.000

'O14'

'E015'

Fri Apr 07 2023 00:00:00.000

'O15'

e_celonis_CreateDeliveryItem

ID : string

TIME : date

DeliveryItem_ID : string

SalesOrderItem_ID : string

'E100'

Fri Feb 03 2023 00:00:00.000

'100'

'O10'

'E101'

Sat Mar 04 2023 00:00:00.000

'101'

'O11'

'E102'

Sun Mar 05 2023 00:00:00.000

'102'

'O12'

'E103'

Mon Mar 06 2023 00:00:00.000

'103'

'O13'

'E104'

Thu Mar 16 2023 00:00:00.000

'104'

'O14'

'E105'

Sun Apr 16 2023 00:00:00.000

'105'

null

e_celonis_CreateSalesOrder

ID : string

TIME : date

SalesOrder_ID : string

'E001'

Wed Feb 01 2023 00:00:00.000

'O01'

'E002'

Wed Mar 01 2023 00:00:00.000

'O02'

'E003'

Sat Apr 01 2023 00:00:00.000

'O03'

o_celonis_DeliveryItem

ID : string

SalesOrderItem_ID : string

'100'

'O10'

'101'

'O11'

'102'

'O12'

'103'

'O13'

'104'

'O14'

'105'

'O15'

o_celonis_SalesOrder

ID : string

'O01'

'O02'

'O03'

o_celonis_SalesOrderItem

ID : string

SalesOrder_ID : string

'O10'

'O01'

'O11'

'O02'

'O12'

'O02'

'O13'

'O02'

'O14'

'O02'

'O15'

'O03'

r_e_celonis_CreateSalesOrder_SalesOrderItem

ID : string

SalesOrderItem_ID : string

'E001'

'O10'

'E002'

'O11'

'E002'

'O12'

'E002'

'O13'

'E002'

'O14'

'E003'

null

Foreign Keys

o_celonis_SalesOrder.ID

o_celonis_SalesOrderItem.SalesOrder_ID

o_celonis_SalesOrderItem.ID

o_celonis_DeliveryItem.SalesOrderItem_ID

o_celonis_SalesOrder.ID

e_celonis_CreateSalesOrder.SalesOrder_ID

o_celonis_SalesOrderItem.ID

e_celonis_AddSalesOrderItem.SalesOrderItem_ID

o_celonis_SalesOrderItem.ID

e_celonis_CreateDeliveryItem.SalesOrderItem_ID

o_celonis_DeliveryItem.ID

e_celonis_CreateDeliveryItem.DeliveryItem_ID

e_celonis_CreateSalesOrder.ID

r_e_celonis_CreateSalesOrder_SalesOrderItem.ID

o_celonis_SalesOrderItem.ID

r_e_celonis_CreateSalesOrder_SalesOrderItem.SalesOrderItem_ID

Result

Column1 : string

'O10'

'O11'

'O12'

'O13'

'O14'

'100'

'101'

'102'

'103'

'104'

'105'

See also: