Mapping: Unconnected LKP Return Multiple Columns
Posted by: Vishal Praveen
Mapping to demonstrate how to derive multiple columns from the output of an Unconnected Lookup Transformation.
Overview
Use a Lookup Transformation in a mapping to lookup data in a flat file, relational table, view, or synonym. You can configure a Lookup Transformation connected or unconnected. A connected Lookup receives input directly from the mapping pipeline. An Unconnected Lookup Transformation receives input from the result of an expression in another transformation.A connected lookup can return multiple columns from the same row. But as an Unconnected Lookup is called from another transformation, it can pass only one output value. In some cases it may be preferable to use unconnected lookup, but still require the lookup to return multiple column values. In this mapping you can download a mapping that demonstrates a technique to achieve this.Implementation Details:
- The mapping uses the following file as source :
| Order_no | Customer_ID |
|---|---|
| 11 | 1 |
| 12 | 2 |
| 13 | 3 |
| 14 | 4 |
- The mapping writes to the below target with additional details about the customer. The customer details are derived from the oracle table Customer_Master using an unconnected lookup.
| Order_No | Customer_ID | First_Name | Last_Name | Phone_No |
|---|---|---|---|---|
| 11 | 1 | Martin | Taylor | 9775876122 |
| 12 | 2 | Nelson | Wales | 9812346565 |
| 13 | 3 | Albert | Butler | 9874566434 |
| 14 | 4 | Richard | Clinton | 9564389372 |
- Lookup Source : Customer_Master
| Customer_ID | First_Name | Last_Name | Phone_No |
|---|---|---|---|
| 1 | Martin | Taylor | 9775876122 |
| 2 | Nelson | Wales | 9812346565 |
| 3 | Albert | Butler | 9874566434 |
| 4 | Richard | Clinton | 9564389372 |
| 5 | Sarah | Shane | 9207875432 |
| 6 | Rajat | Kapoor | 9455672327 |
Features
- PowerCenter version 9.1 and 9.5
