5 Replies Latest reply on Mar 10, 2020 2:23 PM by Nico Heinze

    Use input port in lookup override

    Nitish Sharma New Member

      Hi Gurus,

       

      I am working on logic where i need to do lookup on table but the value in source will not match with lookup as there are few other words attached to source value(i cannot cleanse that so out of question).

      Some time back i have implemented somewhat similar logic where i used input port in lookup override so that i can use like operator and match the value and filter it out, so to use input port we have to create uncached unconnected lookup and then use input port as ?input_port?. Below is my lookup override

       

      select lookup_colA as colA from table where colA like '%'||?inout_port?||'%'

       

      but when i run my session same query gets changed and apply on database like this(picked from session log)

       

      Modified Lookup override : select lookup_colA as colA from table where colA like '%'||?||'%' ORDER BY TERRITORY in transform LKPTRANS

       

      Any idea about this, and what wrong i am doing here?

        • 1. Re: Use input port in lookup override
          Nico Heinze Guru

          Honestly I'm puzzled by your description that you've implemented something like this in the past. I've never seen a Lookup work like that. Never.

          What you can do (and what other customers have done in the past) is to use a SQL Transformation. That works fine.

           

          Granted, I'm a PowerCenter guy and honestly don't know whether ICS / IICS provide a SQL transformation. So it may be that my suggestion is completely out of order here. If that's the case, please accept my apologies.

           

          Regards,

          Nico

          • 2. Re: Use input port in lookup override
            user136699 Active Member

            Check the Documentation - "Transformation Guide" -> Lookup Transformation -> Lookup Query -> SQL Override for uncached lookup.

            This has an example of passing in the port name as a part of the WHERE clause of the SQL override of uncached lookup.

            See if that helps. That is the only reference I find to using a port name as a part of SQL override.

            - Girish

            • 3. Re: Use input port in lookup override
              Nico Heinze Guru

              Thanks for that reference, I'll look into this.

              Besides that my suggestion above (use a SQL transformation instead of a LKP) should work (assuming the SQL transformation exists in ICS / IICS).

               

              Regards,

              Nico

              • 4. Re: Use input port in lookup override
                Nitish Sharma New Member

                Thanks for the response Nico, not sure if i mentioned anywhere in my post or posted this query under wrong tag but m query is related to Powercenter and not with ICS or IICS(this refers to Informatica cloud i guess).

                 

                So my actual requirement was, i have source value i.e.

                id          Territory

                ---          ----------

                1           A123,B123,C123

                2           Y111,Y222

                3           X123

                 

                Lookup value:

                Territory

                ---------

                A123

                Y123

                X123   

                 

                So here i want to lookup the territory and if it match then remove the whole id so my output will have:

                id          Territory

                -----------------------------

                2          Y111,Y222

                 

                Here challenge is to do lookup as source having concatenated string, and for obvious reason we cannot use input port in lookup override and more importantly the "like" operator. as i mentioned i have implemented similar kind of operation where we can include the input port if and only if its unconnected uncahed lookup, in that case if you generated sql it will give you something like this:

                select column from lookuptable where lookup column=?input port?

                Try if you want it, and then i can modify query and use like but this doesn't seems to be working may be i did that in 9.5 version and now using 10.2

                 

                Obviously the other way is to normalize my Territory column and then have a lookup and then concatenate back those territories but wanted to avoid all those sorter, agg and so many exp transformation so i wanted to try this method.

                 

                Thanks

                • 5. Re: Use input port in lookup override
                  Nico Heinze Guru

                  Thanks for the clarification.

                  I see two fairly easy ways to achieve this:

                  First you can use a Java Transformation to split up those territory strings into individual strings; that's ugly easy, if you want, I can post the Java code here tomorrow.

                  Second you can use a SQL Transformation to perform a query like this:

                    SELECT ... WHERE ?input_port? LIKE territory

                   

                  In principle I would prefer the first way, this way you can first use a cached lookup, second it's really easy to implement (as mentioned, the Java code is almost trivial for a halfway seasoned programmer).

                   

                  Regards,

                  Nico