4 Replies Latest reply on Aug 3, 2019 2:06 PM by Nico Heinze

    Passing Multiple Values for Entity Criteria Parameter

    Abhinav Bhatnagar Active Member

      Hi,

       

      In Informatica TDM I have created an entity and using criteria parameter to filter the records. Parameter file is created and I have multiple values for this criteria parameter. Below is my  parameter file

      [GLOBAL]

      $$DEPTNO=00004,023011,180002

      Criteria parameter $$DEPTNO is created in TDM with data type as numeric ,default value zero, override yes.

       

      I created a plan which uses  the entity and generated the workflow. I get below  error message when I ran the workflow "

       

      [VAR_27056 Data conversion error in converting[00004,023011,180002],

       

      When ran the workflow using single value for $$DEPTNO (ie $$DEPTNO=00004 in the parameter file) it ran successfully.

       

      Can't we pass multiple values for a criteria parameters in parameter file? Am I using correct method to pass multiple values?

      Any alternate solution if it can't be?

       

      Thanks,

      Abhinav

        • 1. Re: Passing Multiple Values for Entity Criteria Parameter
          pgeorge1 Guru

          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:

          https://kb.informatica.com/howto/2/Pages/109337.aspx

           

          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.

          • 2. Re: Passing Multiple Values for Entity Criteria Parameter
            Krishna Lingamallu Active Member

            Hi Abhinav,

            There are two types of parameters:

            1) $Param_<name>

            2) $$Value

             

            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

            [GLOBAL]

            $Param_DEPTNO=00004,023011,180002

             

            Thanks,

            Krishna

            • 3. Re: Passing Multiple Values for Entity Criteria Parameter
              Abhinav Bhatnagar Active Member

              Hi Krishna,

               

              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

               

              Thanks

              • 4. Re: Passing Multiple Values for Entity Criteria Parameter
                Nico Heinze Guru

                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).

                 

                Regards,

                Nico