Skip to main content

Add lookups

A lookup resolves a foreign-key column back to the right row in the target database at deploy time. Use them whenever your table references another table by a transient identity key.

Why lookups exist​

Alternative keys solve the problem of porting a row's own identity across environments. Lookups solve the other half: references to other rows.

Example: ORDER_ITEM has a PRODUCT_ID column that points at PRODUCT. The ID is transient: PRODUCT_ID = 5 in dev might be PRODUCT_ID = 12 in UAT. A deployment that hard-codes PRODUCT_ID = 5 will either fail or silently point at the wrong product.

A lookup tells DataStar: "don't copy the literal ID; find the target row by its business key and substitute the right ID."

Basic lookup​

<Column Name="PRODUCT_ID">
<Lookup Table="PRODUCT" Column="ID">
<Criteria Column="PRODUCT_CODE"/>
</Lookup>
</Column>

What this means:

  • Table. The table to look the ID up in (the parent table).
  • Column. The column in that table holding the ID we want.
  • <Criteria>. The business-key column(s) used to find the right row.

At extract time, DataStar reads PRODUCT_CODE from the source database. At deploy time, the generated script uses that code to find the matching ID in the target's PRODUCT table and substitutes it. The literal ID value is never deployed.

Compound criteria​

If one column isn't enough to uniquely identify the lookup, add more:

<Column Name="PARENT_ID">
<Lookup Table="PARENT_TABLE" Column="ID">
<Criteria Column="REGION"/>
<Criteria Column="CODE"/>
</Lookup>
</Column>

DataStar joins on all criteria columns together.

Filtering the lookup​

Sometimes a column can reference one of several parent tables (a discriminated reference). Scope the lookup with a filter:

<Column Name="PARENT_ID">
<Lookup Table="PARENT_TABLE" Column="ID">
<Criteria Column="NAME"/>
<Filters>
<Value Column="TYPE" Value="A"/>
</Filters>
</Lookup>
</Column>

Here, only rows where TYPE = 'A' in PARENT_TABLE are candidates for the lookup.

Applying a lookup to only some rows​

A filter narrows the rows of the lookup table. When narrows the rows of the table you are extracting, for a column that holds different kinds of value by row: a property table, say, whose PROP_VALUE holds a view's id on HoverLayout rows and unrelated values on the others. Needs DataStar 3.1.7 or later.

view-properties.xml
<DataComponent Name="view-properties" Category="view-properties" MinDataStarVersion="3.1.7">
<Table Id="1" Name="VIEW_PROPERTY" Alias="VP">
<Column Name="PROPERTY_ID" PrimaryKey="true"/>
<Column Name="PROP_VALUE">
<Lookup Table="VIEW_DEF" Column="VIEW_ID" ReferenceId="ViewReference"
When="PROP_NAME = 'HoverLayout'">
<Criteria Column="VIEW_NAME"/>
<WhenNotFound Action="Warn" Assign="-1"/>
</Lookup>
</Column>
</Table>
<Reference Id="ViewReference" Variable="${ID}">
SELECT 'views', VIEW_NAME FROM VIEW_DEF WHERE VIEW_ID = ${ID}
</Reference>
</DataComponent>

Suppose the source's VIEW_DEF holds Hover A as 1, Hover B as 2 and Gone as 3, and the target's holds Hover A as 51 and Hover B as 52, and no Gone. Deploying gives:

PROP_NAMEPROP_VALUE in the sourceWhat happensPROP_VALUE in the target
HoverLayout1Looked up: Hover A51
HoverLayout2Looked up: Hover B52
HoverLayout3Looked up: Gone is not in the target, so WhenNotFound warns and assigns-1
HoverLayout-1No view has this id in the source, so it keeps its value-1
FixedFalseNot looked upFalse
Note2Not looked up, although 2 is a view's id in the source2
NoteNULLNot looked up, and Assign does not touch itNULL

Only the HoverLayout rows can report a dependency. The Note row holding 2 does not make this component depend on Hover B.

In a data document, a row whose lookup found its row holds null for PROP_VALUE beside what the lookup found, as the id only means something in one environment. Every other row holds its own value, so the HoverLayout row holding -1 keeps it, and a row pointing at a view that no longer exists in the source keeps its old id.

How it behaves:

  • Write it as a WHERE clause on the source table, with its column names. DataStar qualifies them when it reads the source, bracketed or quoted names too, so a lookup table with a column of the same name is not ambiguous. Leave comments out, as it is written into the middle of a statement. Do not add an alias yourself: the deployed script tests the same text against the rows it has staged, where your alias means nothing.
  • Name columns whose values travel with the row. The test runs on the source when extracting and on the staged rows when deploying, so it cannot rely on a generated key or on a column another lookup fills in at deploy. Naming a source column the script does not stage (Transient="true" or Include="false") stops scripting and says so.
  • Rows are guarded, not just filtered. The value is compared with the lookup's key only on rows When selects, so text such as False on another row does not cause a conversion error against a numeric key. A row When does select must hold a value the key can be compared with.
  • WhenNotFound respects it. Its count and its Assign cover only the rows When selects.
  • Leave Transient off the column. A column whose lookups all have a When keeps its own value on other rows, and Transient="false" means the same. Transient="true" would write NULL for every row, so a template that sets it is reported as invalid. A column with any lookup that has no When is treated as transient unless it says otherwise.
  • It belongs on a column's own lookup. On a lookup inside an <Xpath>, the template is reported as invalid, and scripting stops saying has When, which only a column's own lookup can have.
  • It cannot be on a key that deploys changesets. A key kept beside a lookup holds a different value in each environment, so such a template is refused for changesets.
  • Set MinDataStarVersion="3.1.7" on the template. Older versions ignore When and apply the lookup to every row, so the other rows would lose their values. A document that uses it is written in a format they refuse to read, but extracting with one is not stopped. Keep DataStar.Tools in your pipelines up to date as well: see Requiring a version of DataStar.
  • Extract again after adding it. Documents and scripts already extracted were read without it.

Reporting what a lookup depends on​

Find Dependencies, Find Referencing Components and the order of a deployment file only know about a lookup that names a <Reference>. ReferenceId on the lookup points at the reference's Id, and the reference is SQL returning the category and name of the component that a looked-up value belongs to, in that order. Variable is replaced with the value. The name has to be the name of the other component in that category. The example above uses:

<Reference Id="ViewReference" Variable="${ID}">
SELECT 'views', VIEW_NAME FROM VIEW_DEF WHERE VIEW_ID = ${ID}
</Reference>

When to add a lookup​

Any column meeting all of these is a candidate:

  1. It's a foreign key (or semantically acts like one).
  2. The referenced table's key is transient across environments.
  3. The referenced table has a stable business key (i.e. its template declares an alternative key).

If any of those isn't true, a lookup won't help. In particular, a lookup against a parent table that doesn't have alternative keys will resolve to whatever ID happens to exist in the source and carry that value over, no better than a plain copy.

Verifying the generated script​

A lookup appears in the MERGE as a correlated subquery in the USING clause:

USING (VALUES (...,
(SELECT ID FROM PRODUCT
WHERE PRODUCT_CODE = 'SKU-100'),
...))

If you instead see a literal ID (..., 5, ...), the lookup isn't being applied; check the Column and Criteria attributes match real column names.

Common mistakes​

  • Pointing the lookup at the wrong table. The Table attribute is the parent (the one being referenced), not the current table.
  • Criteria column isn't unique. If the criteria match multiple rows, the generated subquery returns more than one row and the MERGE fails. Fix by adding more criteria columns or scoping with a filter.
  • Transient="true" on a column whose lookup has a When. It would write the column as NULL on every row, so the template is reported as invalid. Leave Transient off.

See also​