FYI: I am answering this from the PowerCenter perspective as I am not well versed in TDM.
You may want to post this in the TDM group also to get the TDM perspective.
I am not clear on your requirement as always the parameter substitution is one-to-one and wanting to substitute many values for one disctreet value in a mapping/session does not make sense on the surface.
However, there is a way to use a paramter value to be used as an expression and then have "multiple" values used in the expression.
One example of this is using the IN function and set the IsExprVar option to True for the mapping variable in the mapping as per this KB:
However, I am not sure if you can do this in TDM.
Please post in TDM group to get confirmation on how to do this in TDM.
There are two types of parameters:
As the issue is occurring with second type of parameter,try with the first type. There was already a CR raised TDM-7801 for this bug.
Create a parameter file as below
In TDM I am not able to create criteria parameter starting with $Param_XX. In TDM criteria parameters are always prefixed with $$.
Eg If I try to create $Param_DEPTNO in TDM ,actual parameter becomes $$$Param_DEPTNO
First these two things ($Param_... and $$...) are two completely different things and should not be mixed up.
A parameter named $Param_... is a so-called Session Parameter and can be used in session settings (such as file names, directory paths, SQL overrides, and so on). Session parameters must always be set using a parameter file.
A parameter with two dollar signs at the start is a mapping parameter which can be set either via a parameter file, its default value, or via a so-called pre-session variable assignment from a workflow variable (that's set up in the session).
Mapping parameters and session parameters cannot be used interchangeably.
Second I don't know TDM good enough in order to know whether this works in TDM as well, but in PowerCenter itself you have a choice when defining a mapping parameter. This choice is named "IsExprVar" and indicates to the PowerCenter Integration Service that this mapping parameter is not a static text but contains some expression term. The actual value of such a parameter is evaluated when a mapping using this parameter is run. For example, such a mapping parameter could contain the following expression term:
In( deptno, '000004', '023011', '180004')
If TDM allows you to set up a mapping parameter which is evaluated at runtime, then you could feed in the requested department numbers this way.
If TDM does not allow it, then I'm the wrong person to ask here.
And yes, you should really move this question to the Secure Testing forum (which is the TDM forum).