dynamic lookup cache enabled with 2 matched new conditions but newLookupRow is 0

The issue has been resolved following the tips below:-
Dynamic lookup error

Here are common mistakes when configuring a dynamic lookup for insert/update functionality:

• Associated ports are not linked to the correct input port
• Datatype mismatches between the dyn lookup in PowerCenter and the database table
• "Insert else Update" is not selected on the dyn lookup properties tab
• The lookup/output ports in the dynamic lookup are not sent out of the lookup (usually to a router).  Some people send the input ports out, or have the router get the data from the preceeding transformation.  Unless the dynamic lookup sends the lookup/output port out, it will not detect a change.
• Input of a column that will always result in an "update" scenario.  For instance, if your target table has a column named "CREATE_DATE" which gets populated with the SYSDATE at the time that the record was loaded, do NOT send the CREATE_DATE column into the dynamic lookup.  The CREATE_DATE, by definition, will be different at the time that the mapping is running from the data that is already loaded into the database.  The CREATE_DATE should be carried from the transformation directly preceeding the dyanmic lookup into the router, skipping over the dynamic lookup.

9 times out of 10, most developers have tripped up and configured the dynamic lookup incorrectly per the above.



In the scenario where the dyanmic lookup is always resulting in an update flag, look at the following:

• data type mismatches between the lookup and the database
• associated ports are mismatched
• input of a column that will always result in an "update."

In the unlikely scenario that all of the options above are configured correctly, but you are still getting all "update" flags from the newLookupRow, I would bet that you are doing some type of mathematic calculation on a numeric column, frequently this is some sort of dollar calculation. In some cases of mathematical calculation, a rounding error becomes an issue. For example, the database is storing $25.58 for the REVENUE column and you believe that the mapping is inputting $25.58 into the dynamic lookup, but in reality due to the way that the number is stored in the PowerCenter engine, the dynamic lookup is actually seeing $25.58000000000000001. Therefore, you are getting newLookupRow = 2. There are a couple of different ways to fix this issue and they can be found my searching my.informatica.com. Many times, enabling high precision arithmetic will do the trick. Sometimes the issue is caused by an IIF statement that looks like this:

IIF(COUNTRY='USA', REVENUE, 0)

when in reality, it should be:

IIF(COUNTRY='USA', REVENUE, 0.00)



There are also some tricks with using the scientific notation for zero (0e0) that you may want to look into.



I have been working with PowerCenter for many, many years and have always found that alleged 'bugs' with the dyanmic lookup to be attributed to mis-configuration or rounding errors.