Skip to main content

UNION_ALL_PULLBACK

Description

The UNION_ALL_PULLBACK function returns the column corresponding to the index requested from the UNION_ALL table.

UNION_ALL and UNION_ALL_TABLE create a virtual table that consists of the vertical concatenation of the input columns/tables. The origin of the different sections of the input tables are recorded. With UNION_ALL_PULLBACK, a section of a UNION_ALL column/table can be projected back to its input table. This can be used to compute intermediate results based on multiple tables (without requiring a join in the data model) and continue with the intermediate results on one of the input tables.

SYNTAX

UNION_ALL_PULLBACK ( expression, index )

NULL handling

NULL values are preserved.

Examples

[1]

In this example, UNION_ALL_PULLBACK pulls the values back to the 2nd parameter specified in the contained UNION_ALL call, the "activityTable" table.

Query

Column1 

UNION_ALL_PULLBACK ( UNION_ALL ( "caseTable"."orderid" , "activityTable"."activity" ) , 2 )

Input

Output

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

Result

Column1 : string

'A'

'B'

'C'

[2]

In this example, UNION_ALL_PULLBACK pulls the values back to the 2nd parameter specified in the contained UNION_ALL_TABLE call, the "activityTable" table.

Query

Column1 

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

Input

Output

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

Result

Column1 : int

1

1

2

[3]

In this example, we execute additional operators on the UNION_ALL result before pulling it back to the input tables. As INDEX_ORDER incrementally counts up per row, the first two rows coming from the "caseTable" produce an offset of 2 when the values are pulled back to the "activityTable" table via UNION_ALL_PULLBACK.

Query

Column1 

"activityTable"."activity"

Column2 

UNION_ALL_PULLBACK ( INDEX_ORDER ( UNION_ALL ( "caseTable"."caseId" , "activityTable"."caseId" ) ) , 2 )

Input

Output

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

Result

Column1 : string

Column2 : int

'A'

3

'B'

4

'C'

5

See also: