How to avoid Null = Null comparisons in Informatica lookup transformation

Informatica lookup transformation by default evaluates Null = Null condition to be true. This means if you do not explicitly take care of passing Not Null values to lookup input ports then the lookup might return random records from the lookup source. It is not certain if this is a bug or done intentionally by Informatica.

People from Oracle background might not realize this until it is too late because in Oracle Null = Null is evaluated to False.

It is a good coding practice to explicitly check all lookup input ports in an expression transformation for Null values before passing them to the lookup transformation.

The most common way of doing this is to use NVL function to replace null values with default characters. It will ensure none of the input ports are Null.

This is a kind of bug which can easily slip through to production environment. In order to avoid this it is advisable to have this in the review items checklist.


Leave a comment

Your email address will not be published. Required fields are marked *