How to use multiple values in a parameter with Informatica, with IN function/clause

Several times we have to use a list of values for filter. It becomes a tedious job, when the list keep changing frequently. Now using parameter file we can actually reduce the overhead. Just parameterize the filter and change the parameter file whenever the change is required. It is little tricky, when it becomes to use multiple values in a parameter(for IN condition). Especially when you have all numbers or decimals in your list. Lets take an example of a filter condition as following

Product_id IN (3940, 3856, 4050, 2109, 5789)

Your port is a decimal/number data type, where as your filter will become a string as you use comma and parenthesis for it, obviously to create a delimited list.

So, here is a way you can still implement it. Create a parameter on mapping level with datatype string. Make sure you have enough length to accommodate the whole list(may be 500 or so)

$$MP_PRODUCT_ID_LIST

Next use it in your filter condition. If it is in SQL override, replace the list with parameter

Product_id IN ($$MP_PRODUCT_ID_LIST) 

Now comes the tricky part, you have to declare it in your parameter file. This is how we are going to do it. each product_id should be quoted with single quotes.

$$MP_PRODUCT_ID_LIST= '3940', '3856', '4050', '2109', '5789'

You should be able to use it now. I had to go through a lot of experiments, before I could really use it, but I am happy that I figured it out.