1 Reply Latest reply on May 7, 2020 8:32 AM by Nico Heinze

    Pass parameter to Expression Transformation

    asagpariya_ciber New Member

      Hi Team,

       

      i'm using PowerCenter. I know, passing expression to Expression transformation using parameter is not supported in Informatica PowerCenter.

       

      But we need work around, if anyone has used it please let me know.

       

      Below is my requirement.

       

      We have parameter file, which we need to update with START DATE and END DATE. So we designed the mapping as

       

      source(param file) -> lookup -> expression -> target (param file tmp)

       

      We extract complete parameter file content as one row.

      Lookup is we get LAST_ EXTRACT DATE and NEXT EXTRACT DATE value

      In expression we perform logic which replace value of $$XTRCT_LAST_DTM with LAST EXTRACT DATE and $$XTRCT_NEXT_DTM with NEXT EXTRACT DATE

      Create temp file

      Later rename to original parameter file

       

      Now, what we have in Expression transformation, we need to completely parameterized it, so that mapping can be used by anyone with any format they want.

       

      For your reference, below is the expression logic we use, which we need to pass as parameter to Expression transformation.

       

      IIF(SUBSTR(LTRIM(RTRIM(RECORD)), INSTR(LTRIM_RTRIM(RECORD)), '$$XTRCT_LAST_DTM'),16)='$$XTRCT_LAST_DTM', SUBSTR(LTRIM(RTRIM(RECORD)),1,INSTR(LTRIM(RTRIM(RECORD)),'$$XTRCTLAST_DTM')+16 || TO_CHAR(LAST_XTRCT_DTM, 'DD-MON-YY HH24:MI:SS'),

      IIF(SUBSTR(LTRIM(RTRIM(RECORD)), INSTR(LTRIM_RTRIM(RECORD)), '$$XTRCT_NEXT_DTM'),16)='$$XTRCT_NEXT_DTM', SUBSTR(LTRIM(RTRIM(RECORD)),1,INSTR(LTRIM(RTRIM(RECORD)),'$$XTRCT_NEXT_DTM')+16 || TO_CHAR(LAST_XTRCT_DTM, 'DD-MON-YY HH24:MI:SS'),

      RECORD))

       

      If we parameterized it, if someone wants to make any change, can do it without modifying or copying mapping.

        • 1. Re: Pass parameter to Expression Transformation
          Nico Heinze Guru

          Hi there,

           

          passing an expression as a parameter to an Expression transformation (or Filter or AGG or whatever) is quite possible. This works as follows:

          When you define a mapping parameter, define it with type String and (that's the most important detail) check the check box IsExprVar (short for "is expression variable").

          Now when you pass a string for this parameter via a parameter file (or a pre-session assignment from a workflow variable of type String, that works just as well), this string will be treated as an expression term instead of a fixed constant value.

           

          The only catch is that the whole expression term must be indicated as one single text line in the parameter file; you know, you cannot split a parameter value over several lines, the whole expression term must fit in one text line.

           

          For example, you can set up a parameter $$CHECK_COND of type String(1000) and - assuming that there are two input or I/O ports CHECK_TYPE and CHECK_VALUE in the same EXP where you want to use $$CHECK_COND - pass the following line in the parameter file:

           

          [GLOBAL]

          $$CHECK_COND=IN( CHECK_TYPE, 'A', 'C', 'M') AND (CHECK_VALUE = 2000 OR (CHECK_VALUE > 500 AND CHECK_VALUE < 1000))

           

          Of course the parameterised condition must only use ports which are available in the transformation where you want to use $$CHECK_COND.

           

          Regards,

          Nico