Skip to main content

LOOKUP

Description

The LOOKUP operator allows you to lookup matching rows of a source column to a target table ignoring the potentially pre-defined join relationship in the Data Model.

LOOKUP performs a left outer join between a target table and a source column which must have a N:1 relationship. Only equi-joins are supported. Multiple join conditions are combined using AND logic.

Syntax

LOOKUP ( target_table, source_column, (join_condition) [, (join_condition),... ] )

  • target_table: Table to which the source should be joined. The target_table is on the N-side of the join.

  • source_column: Column to be joined to the target_table. The table of source_column is on the 1-side of the join. This cannot be a constant.

  • join_condition: At least one join condition must be provided. If multiple join conditions are provided, these are combined using AND logic. See below for the syntax.

The join_condition consists of columns from the target_table and the source_table where source_table is the table that source_column belongs to:

(target_table.column, source_table.column)

  • target_table.column: A column from the target_table.

  • source_table.column: A column from the source_table.

A single join_condition may be interpreted as target_table.column = source_table.column and multiple join conditions as target_table.col1 = source_table.col1 AND target_table.col2 = source_table.col2.

Filter behavior

LOOKUP does not change how filters propagate. Filters are applied on the LOOKUP result column as if was any other column on the target table.

Examples

[1]

LOOKUP behaves like a BIND with a custom join definition. In this example we check for matching IDs in the respective columns. Note that the target table T2 is semantically on the N-side.

Query

Column1

"T2"."ID"

Column2

LOOKUP ( "T2" , "T1"."VALUE" , ( "T2"."ID" , "T1"."ID" ) )

Input

Output

T1

ID : int

VALUE : string

1

'one'

2

'two'

3

'three'

4

null

T2

ID : int

1

2

2

4

Result

Column1 : int

Column2 : string

1

'one'

2

'two'

2

'two'

4

null

[2]

We can customize the join condition, e.g. by selecting only a specific ID, as long as the common tables of the join expressions match the respective target_table and source_column parameter.

Query

Column1

"T2"."ID"

Column2

LOOKUP ( "T2" , "T1"."VALUE" , ( CASE WHEN "T2"."ID" = 2 THEN "T2"."ID" END , "T1"."ID" ) )

Input

Output

T1

ID : int

VALUE : string

1

'one'

2

'two'

3

'three'

4

null

T2

ID : int

1

2

2

4

Result

Column1 : int

Column2 : string

1

null

2

'two'

2

'two'

4

null

[3]

FILTERs do intentionally not propagate across LOOKUP calls. Even though we filter on a specific"ID" from table "T1", on table "T2", this filter takes no effect due to the tables being unjoined in the original data model. However, directly filtering on the LOOKUP call works. As intended we lose the row where the looked-up value is NULL.

Query

Filter

FILTER "T1"."ID" = 2;

Filter

FILTER LOOKUP ( "T2" , "T1"."VALUE" , ( "T2"."ID" , "T1"."ID" ) ) IS NOT NULL;

Column1

"T2"."ID"

Column2

LOOKUP ( "T2" , "T1"."VALUE" , ( "T2"."ID" , "T1"."ID" ) )

Input

Output

T1

ID : int

VALUE : string

1

'one'

2

'two'

3

'three'

4

null

T2

ID : int

1

2

2

4

Result

Column1 : int

Column2 : string

1

'one'

2

'two'

2

'two'

[4]

A join can also be defined over multiple expressions or columns, even with multiple types. In this example our join consists out of two ID columns which are logically ANDed. Note however that the type within one join_condition needs to match.

Query

Column1

"T2"."ID1"

Column2

"T2"."ID2"

Column3

LOOKUP ( "T2" , "T1"."VALUE" , ( "T2"."ID1" , "T1"."ID1" ) , ( "T2"."ID2" , "T1"."ID2" ) )

Input

Output

T1

ID1 : int

ID2 : string

VALUE : string

0

'1'

'zero-one'

1

'0'

'one-zero'

1

'1'

'one-one'

T2

ID1 : int

ID2 : string

0

'1'

null

'11'

1

'1'

Result

Column1 : int

Column2 : string

Column3 : string

0

'1'

'zero-one'

null

'11'

null

1

'1'

'one-one'

[5]

LOOKUP can partially resolve self-joins. In this example we manually traverse the following, simple graph:

LOOKUP_self.png

In this context, the nodes of the graph may only converge, never diverge, otherwise we break the 1:N requirement of the LOOKUP operator. For improved graph support, please have a look at the Object Link feature.

Query

Column1

"NODE"."CURRENT"

Column2

"NODE"."NEXT"

Column3

LOOKUP ( "NODE" , "NODE"."NEXT" , ( "NODE"."NEXT" , "NODE"."CURRENT" ) )

Input

Output

NODE

CURRENT : string

NEXT : string

'A'

'B'

'B'

'C'

'C'

null

'D'

'C'

Result

Column1 : string

Column2 : string

Column3 : string

'A'

'B'

'C'

'B'

'C'

null

'C'

null

null

'D'

'C'

null

See also: