Informatica Common Components lookup seven find criteria

PowerCenter uses the find criteria to test the received values. This is similar to the WHERE clause in the SQL query. When you configure a lookup condition for a transformation, the values in the transform input values and the lookup source or cache (represented by the lookup port) are compared. When you run the workflow, PowerCenter queries all received values in the lookup source or cache based on the criteria.

You must enter a lookup condition in all lookup transformations. Some guidelines for finding criteria apply to all lookup transformations, and some guidelines depend on how you configure the conversion.

When you enter criteria for a Lookup transformation, follow these guidelines:

• The data types in the condition must match.

• Use one input port for each lookup port used in the condition. You can use the same input port in multiple conditions of conversion.

• When multiple conditions are entered, PowerCenter evaluates each condition as and instead of OR. PowerCenter only returns rows that match all of the conditions that you specify.

• PowerCenter matches a null value. For example, if the input lookup criteria column is Null,powercenter, the null is evaluated to be equal to NULL in the lookup.

• When a flat file lookup is configured for the sorted input, PowerCenter causes the session to fail if the condition column is not grouped. If the columns are grouped but not yet sorted, PowerCenter will handle lookups in a way that you do not have the sort input configured.

The lookup criteria and PowerCenter processing methods may differ depending on whether you configured the transformation to dynamic cache, not cache, or static cache.

No cache or static cache

When you configure the Lookup transformation to be cache-free or use a static cache, follow these guidelines:

• When you create a find condition, you can use the following operators: =, <, >=,, <=,! =

Tip: If more than one lookup condition is included, precede the conditions with the equals sign to improve lookup performance. For example, create the following lookup criteria:

item_id = in_item_id Price <= in_price

• The input value must satisfy all criteria found to return a value.

Conditions can match the equivalent or provide a threshold condition. For example, you need to find a customer who is not living in California State, or an employee with a salary higher than $30,000. Depending on the type of source and condition, lookups may return multiple values.

Working with multiple matches

The find operation looks up values based on the criteria that you set in the Lookup transformation. If the lookup condition is not based on a unique key, or if the lookup source is denormalized, PowerCenter may find multiple matches in the lookup source or cache.

• Returns the first matching value, or returns the last matching value. You can configure the transformation to return the first matching value, or return the last matching value. The first and last values refer to the first and last values found in the lookup cache that match the lookup criteria. When you cache a lookup source, PowerCenter determines which row is the first and which is the last by generating an ORDER BY clause for each column in the lookup cache. PowerCenter Server then arranges each lookup source column in the lookup criteria in ascending order.

PowerCenter sorts the numeric columns in ascending order (such as 0 to 10), from January to December and arranges the date/time columns from the first day of the month to the last day of the month, and arranges the string columns according to how the session configuration is sorted.

• Returns an error. PowerCenter returns the default value for the output port.

Note: When a session encounters multiple keys configured to use a Lookup transformation for dynamic cache, PowerCenter causes the session to fail.

Dynamic cache

If you configure the Lookup transformation to use dynamic caching, you can only use the equals sign (=) operator in the lookup condition.

Working with multiple matches

You cannot configure multi-match processing in a transformation that is configured to use dynamic caching. When a session is caching a lookup table or looking for a value in a cache that contains duplicate keys, PowerCenter will fail the session if it encounters a multiple match.

Informatica Common Components lookup seven find criteria