Skip navigation
Contact Us
4328 Views 7 Replies Latest reply: Jul 3, 2013 10:35 AM by Sridhar M RSS
Michael Murray Newbie 5 posts since
Jul 24, 2007
Currently Being Moderated

Aug 4, 2008 8:33 AM

Lookup Policy on Multiple Match Infa 8.1.5+


What is the difference in the Lookup Policy for Multiple Matches within a lookup between: Use First Value, Use Last Value and Use Any Value. When there are multiple rows with the same key does Informatica do additional processing to determine which row is the 'true' first' It would seem that Use Any Value would be a better when checking the condition.


Thanks.

  • William Brooks Newbie 19 posts since
    Jul 16, 2003
    Currently Being Moderated
    Aug 6, 2008 10:35 AM (in response to Michael Murray)
    Lookup Policy on Multiple Match Infa 8.1.5+

     

    In a lookup, Informatica automatically inserts an ORDER BY clause that sorts the returned rows in ascending order by the lookup key (specified on the condition tab).  The FIRST and LAST are ordered by these values.  I believe that when ANY is used, the cache file is just processed in the quickest manner possible, returning "any" value.

  • Gunjesh Labh Newbie 12 posts since
    Jun 27, 2006
    Currently Being Moderated
    Mar 1, 2009 7:39 AM (in response to Michael Murray)
    Look-up Multiple Match : Report Error

    If i set in session property that Stop on error: 1 and selecting multiple match policy to "Return Error" then it will stop entire session/workflow if multiple records found for given conditions in look-up. My requirement is that whenever multiple records found then return NULL on all output ports...  is that possible ?? I don't want it to be look-up query over-ride way (i.e making look-up query such that it will have only one record for the condition field).

    • Nishant Varadachari Apprentice 138 posts since
      Mar 28, 2013
      Currently Being Moderated
      Jul 3, 2013 6:46 AM (in response to Gunjesh Labh)
      Look-up Multiple Match : Report Error

      Hello Gunjesh,

       

      set the lookup policy on multiple match as Report error. this returns the default value for the port. In the expression where you are calling the lookup, setup the default value as 'error' or 0 and then check for them. then all you have to do is check the error code and issue an abort in the mapping itself. I am not sure this will work but give it a try.

       

      OR

       

      Do one thing..

      In the sql override, write the sql and add a new port which will be the count of rows (U need to use the oracle analytical functions fot calculate the count for a group).

      For this you need to create another port in lookup to hold this value.

       

      Now you can pass this count to an expression and check if it is > 1 and if so, you can abort the session.

       

      let me give an example for count() function.

       

      select empid, ename, deptno,

      count() over(partition by deptno) as countn

      from emp

       

      For any help on oracle analytical function please refer some good oracle sites.

       

      Hope this solves your problem Do one thing..
      In the sql override, write the sql and add a new port which will be the count of rows (U need to use the oracle analytical functions fot calculate the count for a group).
      For this you need to create another port in lookup to hold this value.

       

      Now you can pass this count to an expression and check if it is > 1 and if so, you can abort the session.

       

      let me give an example for count() function.

       

      select empid, ename, deptno,
      count() over(partition by deptno) as countn
      from emp

       

      For any help on oracle analytical function please refer some good oracle sites.

       

      Hope this solves your problem

  • Sridhar M Novice 64 posts since
    Jul 13, 2011
    Currently Being Moderated
    Jul 3, 2013 10:35 AM (in response to Michael Murray)
    Lookup Policy on Multiple Match Infa 8.1.5+

    Hi Michael,

     

    Please refer below:

     

    Use the first matching value, or use the last matching value:

    You can configure the transformation to return the first matching value or the last matching value. The first and last values are the first value and last value found in the lookup cache that match the lookup condition. When you cache the lookup source, the Integration Service generates an ORDER BY clause for each column in the lookup cache to determine the first and last row in the cache. The Integration Service then sorts each lookup source column in ascending order. The Integration Service sorts numeric columns in ascending numeric order such as 0 to 10. It sorts date/time columns from January to December and from the first of the month to the end of the month. The Integration Service sorts string columns based on the sort order configured for the session.

     

     

    Use any matching value:

    You can configure the Lookup transformation to return any value that matches the lookup condition. When you configure the Lookup transformation to return any matching value, the transformation returns the first value that matches the lookup condition. The transformation creates an index based on the key ports instead of all Lookup transformation ports. When you use any matching value, performance can improve because the process of indexing rows is simpler.

     

     

    Return an error:

    When the Lookup transformation uses a static cache or no cache, the Integration Service marks the row as an error. The Lookup transformation writes the row to the session log by default, and increases the error count by one. When the Lookup transformation has a dynamic cache, the Integration Service fails the session when it encounters multiple matches. The session fails while the Integration Service is caching the lookup table or looking up the duplicate key values. Also, if you configure the Lookup transformation to output old values on updates, the Lookup transformation returns an error when it encounters multiple matches. The transformation creates an index based on the key ports instead of all Lookup transformation ports.

     

    Thanks,

    Sridhar.

More Like This

  • Retrieving data ...

Bookmarked By (0)