Unconnected lookup in Informatica - Troubleshooting

Unconnected lookup in Informatica - Troubleshooting

Kept running into the below error message when I was using unconnected lookup and finally resolved it with proper setting on the lookup properties. In the below blog, I'm sharing the proper settings for unconnected lookups:

Transformation: exp_job_control Field: target_count

<<PM Parse Error>> [:LKP.lkp_counter_work(1),'2')]: : invalid function reference

  

The unconnected lookups are called from expression or some filter transformation like a function call. To call it as a function, you need to pass some input and get a return value. To call an unconnected lookup in informatica we use the below syntax:

:LKP.lookupname(parameter1, parameter2,..)

For example below it would be:

:LKP.lkp_target_work(employee_id)

The value returned from the lookup can be used in some expression or assigned to the port. 

The lookup transformation properties would like the screenshot below: In the lookup emp_id and emp_dept are from the look up table and input_emp_id is the input column whose value is sent by the calling lookup function. 

The correct setting is that the lookup table columns should be marked as output and lookup port and one of it should be marked as return port.  The input port should be marked as Input port.

  The join condition for the above lookup would be:

Fetching Row Count using Unconnected Lookup in Informatica:

If you want to get row count of a table using unconnected lookup then use the below sql is in your lookup SQL.

SELECT 1 as input, count (*)  as target_count from <tablename>

The join condition would be on 1 which is port input.

The lookup call in the expression is:

:LKP.lkp_name(1)