Lookup transformations

This page is kept in the importer's repository, next to the code it describes, and copied here.

A PowerCenter Lookup becomes a Hop Database join: a query with one ? per lookup condition, run for each incoming row, with the result fields added to the row.

What a Lookup looks like in a repository export

<TRANSFORMATION NAME="lkp_PAY_PERIOD" TYPE="Lookup Procedure">
  <TRANSFORMFIELD NAME="in_CURR_PP_FLAG" PORTTYPE="INPUT"/>
  <TRANSFORMFIELD NAME="PP_NUM" PORTTYPE="LOOKUP/OUTPUT"/>
  <TRANSFORMFIELD NAME="PP_END_YEAR" PORTTYPE="LOOKUP/OUTPUT"/>
  <TRANSFORMFIELD NAME="CURR_PP_FLAG" PORTTYPE="LOOKUP"/>
  <TABLEATTRIBUTE NAME="Lookup Sql Override" VALUE=""/>
  <TABLEATTRIBUTE NAME="Lookup table name" VALUE="PAY_PERIOD"/>
  <TABLEATTRIBUTE NAME="Lookup Source Filter" VALUE=""/>
  <TABLEATTRIBUTE NAME="Lookup condition" VALUE="CURR_PP_FLAG = in_CURR_PP_FLAG"/>
  <TABLEATTRIBUTE NAME="Lookup policy on multiple match" VALUE="Use Any Value"/>
  <TABLEATTRIBUTE NAME="Lookup caching enabled" VALUE="YES"/>
  <TABLEATTRIBUTE NAME="Connection Information" VALUE="$Target"/>
</TRANSFORMATION>

Port type	Meaning
INPUT	A value coming in, compared in the lookup condition (often named in_…)
LOOKUP	A column of the lookup table or override
LOOKUP/OUTPUT	A column that is also returned
LOOKUP/RETURN/OUTPUT	The return value of an unconnected Lookup
INPUT/OUTPUT	A value that passes through

The query

PowerCenter applies the lookup condition on top of the table or the SQL override, so both become one query:

SELECT lkp.PP_NUM, lkp.PP_END_YEAR
FROM PAY_PERIOD lkp                      -- or: FROM (<the override>) lkp
WHERE lkp.CURR_PP_FLAG = ?
  AND (<Lookup Source Filter>)           -- when there is one

• Each ? is bound to the stream field of the lookup input port the condition names (CUST_ID = IN_CUST_ID binds IN_CUST_ID).
• The row limit is 1 and the join is outer: one row per input row, with nulls when nothing matches — what a PowerCenter lookup returns.
• "Use First Value" / "Use Last Value" become an ORDER BY on the returned columns, ascending or descending, so the row returned is deterministic.
• The connection is the one the session binds the lookup to, or the session's $Source / $Target connection when "Connection Information" says $Source / $Target.
• Returned fields are renamed after the join in a Select Values (<lookup>.rename) when their name differs from the column: Hop names query fields after the column the JDBC driver reports, which is not the alias for every driver.

An override that already contains ? placeholders is used as written, binding the input ports in order. That is the shape of the older hand-written fixtures in src/test/resources/fixtures/powercenter/ (SELECT Id, Name, Type FROM Account WHERE Id = ?, no lookup condition); PowerCenter does not export it.

Connected lookups

The Database Join sits where the lookup sits in the mapping. Its input ports arrive under their own names through the connection's Select Values, and its lookup/output ports leave under theirs.

Unconnected lookups

An unconnected lookup has no hops; expressions call it as :LKP.name(args). The importer inlines it into every Expression that calls it:

exp_Initial.lookup arguments → exp_Initial.lkp_LESC_KEY → exp_Initial → exp_Initial.ports

• Arguments that are ports of the expression are bound directly; anything else (a literal, an expression over input ports) is computed first in <expression>.lookup arguments.
• The Database Join returns the lookup's RETURN port as <lookup>_<return port>, which the translated formula reads where the :LKP call was.
• Helper fields are dropped again in <expression>.ports.
• An argument computed from a variable or output port of the same expression cannot be bound before the Formula runs; that port is reported for manual work.

An unconnected lookup that no expression calls does nothing in PowerCenter and is not converted.

Limitations

1. Caching. Database Join queries once per input row. For a large stream against a small, static lookup table, a Hop Database Lookup with caching or a Stream Lookup is faster.
2. "Report Error" on multiple match is not reproduced: the first matching row is used.
3. Dynamic and persistent lookup caches (lookups that see rows the session inserts, or keep their cache between runs) are not reproduced, and neither are lookups on flat files.