2 Replies Latest reply on Feb 28, 2021 4:07 PM by Thiru S

    Parameter as port name in Expression

    hayes5736 Active Member

      I'm working in DEI 10.4.0.1. I was wondering if there is a way to use a parameter as a port name in an expression of a dynamic mapping.

       

      I have a lookup transformation that passes a port to my expression named message_id_LKP. This lookup will tell me if my source row exists in the target. If it exists I will potentially update the target else I will insert the row.

       

      I have a parameter set up named, param_lookup_key_column with a default value of message_id_LKP.

       

      I want to create an expression like this: IIF(ISNULL($param_lookup_key_column), 'INSERT', 'UPDATE')

       

      So, at run time I want the parameter to be expanded (to the port name) before the expression is evaluated.

       

      I remember something from my PowerCenter days about IsExpVar. Is there some way to force the parameter to be expanded prior to evaluating the expression?

       

      Currently, the expression ends up evaluating ISNULL('message_id_LKP')

        • 1. Re: Parameter as port name in Expression
          Vlad Ponomarenko Guru

          Hi,
          You can use Parameter of Expression Type and set it to 
          IIF(ISNULL(<Port_Name>, 'INSERT', 'UPDATE')
          where <Port_Name> is your needed port name.

          Regards, Vlad 

          • 2. Re: Parameter as port name in Expression
            Thiru S Active Member

             

            In certain scenarios, when a static parameter is used inside expression as a variable, it might not be evaluated properly.

             

            For such kind of use cases, if possible, we can define 'complete expression logic' as an Expression parameter and then pass the expected expression logic as a 'value' at runtime.

             

            E.g.

             

            If value for the parameter - 'param_lookup_key_column' - is determined at the time of mapping execution as 'message_id_LKP', then following logic could be tried.

             

             

            Before:

             

            IIF(ISNULL($param_lookup_key_column), 'INSERT', 'UPDATE')

             

            After:

             

            $dynamic_expr

             

             

            In the mapping - dynamic_expr - should be parameter of 'Expression'  type and for that value can be passed as below:

             

            IIF(ISNULL(message_id_LKP), 'INSERT', 'UPDATE')