Advanced ETL Processor's "Is In list" validation function is used to check if a value exists in the database or file.
But In some situations, it is not very efficient.
- The list of values can be too big and it would not possible to fit it into the memory.
- Pulling entire lookup may take to much time
- We may want to process just 10 records but our lookup has 10 million records
In this case, there is a better alternative "Is value in database" and "If value in database" transformation functions
The way it works is very simple: during execution <value> is replaced with actual value If number of records returned is more than zero it returns success otherwise failure