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.
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.
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).
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.
So here i want to lookup the territory and if it match then remove the whole id so my output will have:
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 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).