5 Replies Latest reply on Jun 11, 2021 11:17 AM by Nico Heinze

    How to read an expression from a string and execute it?

    Alex Varela New Member

      Hello everyone,


      So I'm I want to save a text in the database and replace ports with ports values on a expression. For example:

      'Today's date is: ' || n_StringDate, so the idea is having that stored in a db table, and then execute that in a expression that has the port n_StringDate, and set the value that port has. I've been reading about the variables set to IsExprVar to true, but I always get the error:

      Severity Timestamp Node Thread Message Code Message

      ERROR 7/6/2021 10:35:41  MAPPING TE_7002 Transformation Parse Fatal Error; transformation stopped...

      TE_7002 [<<PM Parse Error>> [SETVARIABLE]: first argument can reference to user-defined mapping variable only.

      ... SETVARIABLE(>>>><<<<, StringExpression)]



      So it is evident I'm doing something wrong. How can I achieve this?

        • 1. Re: How to read an expression from a string and execute it?
          Nico Heinze Guru

          If you have defined a mapping parameter(!) with the flag IsExprVar checked, then you can simply use this mapping parameter as an expression.


          For example, let's suppose you have a mapping parameter $$EXPRESSION of type String(2000).


          In a parameter file, you set this mapping parameter like this:

          $$EXPRESSION='Today' || Chr( 39) || 's date is: ' || n_StringDate


          Now in an EXP you can set up an output port o_Today of type String(2000) with this expression term:



          That's all.

          Unfortunately (as always with mapping parameters) this way you can have one one single "constant" expression per mapping parameter during each session run. If, for example, you need 20 different expression terms being parameterised, then you will have to define 20 different mapping parameters (all with IsExprVar checked) and use them accordingly.




          • 2. Re: How to read an expression from a string and execute it?
            Alex Varela New Member

            Thank you very much for your answer! Yes, I do have a mapping variable (not parameter) on my map with the isexpvar value set to true, on the research I've made the parameter file appears a lot, what does that mean? Do I have to pass the expression I want through a File? Can't I take the value from the DB? I've just begun using PowerCenter so I'm lost with all the properties that can and can't be used for any purpose.


            So what I'm trying to solve is the creation of a Message using ports to replace the values each message I have, that's why I had this idea of simply saving the script that will build my message. Is there any other approach to do this easly in PowerCenter?

            • 3. Re: How to read an expression from a string and execute it?
              Nico Heinze Guru

              OK, first a bit of clarification.

              A mapping variable is a value which can (and usually will) be changed during a session run; when the session ends successfully (including all post-session commands and so on), the last value of the variable will be saved in the repository for the next session run.

              What you want to use is a so-called mapping parameter. This is a value which is fed into the session when it starts; this value remains constant as long as the session runs, you cannot change it.


              Now I need a bit of clarification from you. Could you please post an example (not in tech speech but in "business-oriented" language) what exactly you're trying to do? You see, I understand PowerCenter, but I don't know your environments or tasks. Hence I dare to ask for an example of let's say 3-5 input records and what you want to get out of these input records.




              • 4. Re: How to read an expression from a string and execute it?
                Alex Varela New Member

                I think I got it, maybe with an example it would be crystal clear.


                So, what I'm trying to do is parameterize some messages in a DB. Having a message that uses ports names were values have to be replaced. An example of that is:

                So assuming I got the script I want from my table, I was trying to use a map variable as an expression so I could assign the script from the table and execute it using the variable. Like this:



                That's what I was trying to do. And I don't know if it is possible, from what I understand of your explanation it isn't. But if there is an alternative to do it wihout a replace and in a dynamic way, I would appreciate your help.


                Thank you.

                • 5. Re: How to read an expression from a string and execute it?
                  Nico Heinze Guru

                  We're talking about completely different things here. I was talking about mapping variables and mapping parameters while you are talking about variable ports in some transformation (e.g. in an EXP or an AGG).

                  You can build variable ports holding such output strings, that's not the point, but I'm still not sure whether this is really what you want.


                  I'll post an example with some mapping parameters later (probably by Sunday or so, still have some urgent work to do). In the meantime may I suggest that you get hold of the Designer Guide for PowerCenter (that's part of the PowerCenter documentation set, a heap of PDF files, to be downloaded e.g. from the Informatica Network home page under the heading Documentation). There you will find a chapter about mapping variables and mapping parameters. Again, what you are trying to do MAY be achievable using mapping parameters, so don't waste your time looking at mapping variable, focus on mapping parameters.


                  Still I would appreciate if you could describe the business process you're trying to build in plain text. Don't use any technical terms (as far as possible), describe in plain text what you want to produce in the end. This might help in clearing the misunderstandings that obviously do exist at this point in time.