2 Replies Latest reply on Jan 14, 2019 5:56 AM by Nico Heinze

    Pass list of values as param

    E S New Member

      Hi ,

       

      I would like to pass below value as param to mapping. But it seems powercenter doesnt like the commans or single quotes in the values.

       

      $$ListParam=( 'C', 'S', '1', '9' )

       

      Please help.

       

      Eventually I would like to pass this param to a SQl query as select * from abc where COL1 in $$ListParam

       

      Thanks,

      E

        • 1. Re: Pass list of values as param
          Syed Aziz Active Member

          You can try out the below steps to implement this:

          In a workflow,

          1. Add a Mapping task which runs your logic and creates a target file, say tgt_a.txt. You can use the Output type as File and parameterize the OutputDirectory according to your requirement.

          2. Next add a command task with the required queries.

           

          • 2. Re: Pass list of values as param
            Nico Heinze Guru

            Syed, could it be that you're responding for ICS/IICS? The question is for PowerCenter.

             

            @E S, if this is for a relational Source Qualifier, then please check the session log; it should clearly state what SQL query has been issued against the source database, so you should be able to see what "typo" you may have in the parameter file or what other mistake took place.

             

            If this is for a SQL Transformation, then I assume that you've given the query as

                select a, b, c from table1 where col1 in $$ListParam

            And to be honest I really don't know whether you can use mapping parameters in a SQL transformation this way. You may try to forward this mapping parameter to the SQLT from e.g. an Expression transformation as an additional output port Where_Clause, then the query would look like this:

                select a, b, c from table1 where col1 in ?Where_Clause?

             

            Alternatively you might want to go for the following approach (which is more performance- consuming but 100% safe to use):

            Set up an Expression transformation right before the SQLT.

            In this EXP, construct the whole SQL query as one single string. Let's assume you name the respective output port containing the string as "Query". Let's also assume the query looks like this:

                select a, b, c from table1 where col1 in ('C', 'S', '1', '9')

            Now you forward the port Query to the SQLT.

            In the SQLT, you set up the query like this:

                ~Query~

            (meaning this query is interpreted and "freshly" executed by the DBMS for each and every single input record)

             

            Of course this assumes that all output ports of the SQLT have exactly the same names as the attributes in the SELECT list; in my example this means that the output ports have to be named A, B, and C (in this order).

            If not, you have to construct the query such that each selected value has the correct alias definition for the respective output port. For example, let's assume the table1 has attributes COL2, COL3, and COL4; let's further assume that (for whatever reason) you want to retrieve these attributes under the port names A, B, and C; then the SQL query must look like this:

                select col2 as A, col3 as B, col4 as C from table1 where COL1 in ('C', 'S', '1', '9')

             

            Regards,

            Nico