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.

How to create and use a Parameter file in Informatica

              I know how tedious and painful it is to change and migrate the code every time you have to do a little change. On the other hand it is not a best practice to use hard coded values in the mappings. It is always useful to create a generic code, so that it can be used as per requirements with a little change. So, today I will be talking about parameterization in Informatica. There are actually two types of parameters in Informatica based on the scope.

Workflow parameters-defined on the workflow level
Mapping parameters-defined on the mapping level

I am going to take an example here to describe both of these types of parameters. Let's say we have a requirement to use a filter on some port (field/column). It should be a mapping level parameter if the filter is only going to be used in this mapping and not in the other mappings(with same value) under the same workflow. So we will define the parameter by following the steps shown in pictures here. Say we have the filter like Dept='SALES'. This tells me that it is a string data type and length can be 10.
$$MP_DEPT

     
                                  

                         

Once you have defined the parameter, lets use it in the transformation, say SQL qualifier, we can use it as

Dept=$$MP_DEPT

Now the next step is to create a parameter file. write similar text in a text file, save it as test.prm or the name you wish.

[GLOBAL]

$DBConnection=ORA1
$$MP_DEPT='SALES'

$DB_Connection here is a workflow parameter and used for providing connection for in workflow.
Next is to give the path of parameter file in the your workflow. Right click on your workflow. Click on edit workflow and select Properties tab.

Now edit the Parameter Filename attribute. Remember to provide the full path of your parameter file along with the name.

Good to go. Have fun with your creativity and parameterization.

For steps creating a workflow parameter, I will write a separate post.