ODI - How to use lookup component

Lookup:

• A lookup is a selector component that returns data from a lookup flow being given a value from a driving flow.The attributes of both flows are combined, similarly to a join component. 
• A lookup can be implemented in genereted SQL either through a Left outer join or a nested select statement.
• A lookup condition can be created by dragging an atrribute from the driving flow and then the lookup flow onto the lookup component. The lookup condition is an equi-join between the two attributes.
• The lookup component gives us some additional functionality compared to a join. We can supply a default value for a key that does not exist in our lookup table. Using join, we might have to do an outer join followed by an expression operator providing a CASE statement to replace the NULL from the unmatched key.
• Using lookup, we can define match row rules and no match rows too.

Implementation Steps:

Step1: In Topology, create physical and logical architecture and corresponding schemas and context.

Step2: In Designer tab, Create models and revers engineer to get the required data store tables.

Step3: Create a project and create a map.

Step4: Drag and drop the departments and employees tables and connect with the lookup.

Step5: provide the lookup condition. 

Step6: drag and drop the target table EMP_Depart and connect with the lookup.

Step7: go to the connectors point and see input and ouput connectors.

Driver table: Departments

LOOKup table: Employees

Step8 : select sk field and edit expression and use nextval sequence.

Step9: uncheck update.

Step10: add the optimization context.

Step11: Add LKM=LKM SQL to SQL for lookup

Step12: add IKM for target table and make flow control as false.

Step13: save. Run and check operator tab and target db table .