Rlookup
Relational functions rlookup
syntax
- rlookup(foreign_key, primary_key)
definition
rlookup(foreign_key, primary_key) results in a relation from the Domain unit of the foreign_key Argument towards the domain unit of the primary_key argument.
applies to
- foreign_key: an Attribute which can serve as primary key for the domain unit of the primary_key argument, e.g. a region code.
- primary_key: an attribute which can serve as primary key for its own domain unit and with the same Values unit as the foreign_key attribute.
- attributes foreign_key and primary_key with Numeric, Point, uint2, uint4, bool or string Value type
description
We advise that the second argument can be used as primary key for the domain unit of this argument.
If multiple instances of the second argument occur, the resulting value will be the first index number found.
value info
The [[value info]] dialog traces a resulting value back to the primary_key row that was found, by showing the foreign_key value at the explained row and then the row of primary_key it matched. When the result is null it adds no row of the searched attribute has this value — unless the foreign_key value at that row is itself null, which the trace already shows. (since 20.16.0)
conditions
The values unit of the arguments foreign_key and primary_key must match.
performance
O(n + m) where n = size of foreign_key domain and m = size of primary_key domain. Builds an index on primary_key values, then looks up each foreign_key. For sorted integer keys, uses direct indexing; for other types, uses hash-based lookup.
example
attribute<Region> Region_rel (City) := rlookup(City/RegionCode, Region/RegionCode);
| City/RegionCode | Region_rel |
|---|---|
| 100 | 0 |
| 200 | 1 |
| 300 | 2 |
| 200 | 1 |
| 400 | 3 |
| null | null |
| 400 | 3 |
domain City, nr of rows = 7
| Region/RegionCode |
|---|
| 100 |
| 200 |
| 300 |
| 400 |
| 500 |
domain Region, nr of rows = 5