Informatica Integration service runs a default SQL statement when the first row enters the lookup transformation in the pipeline.
Default Lookup Query
If there is no SQL override and no filter specified the integration service generates a default SQL to query the lookup. It contains following components:-
SELECT:- The select statement includes all the lookup ports in the lookup transformation. You can view the columns of the default SQL if you generate the SQL override query.
ORDER BY:- All columns in the lookup transformation are contained in the order by clause. They appear in the same order as in the transformation. The order by clause does not appear when you generate the SQL override of the default query, but it is present to take effect when integration service executes the session.
SQL Override
If the lookup source is a relational table, you can override the default lookup query by using the SQL Query Override. Following scenarios warrant for overriding the default lookup query:-
1. When you wish to override the default ORDER BY clause to improve the performance. You may choose to have less number of columns in the order by clause as compared to what integration service generates by default. When overriding the default Order by clause, always put a comment notation ‘–‘ at the end of the SQL query override. This will comment out the default Order By clause. If you do not specify the comments notation the integration service will fail the session at run time.
(The exception is when you use pushdown optimization- in such cases you cannot override the default ORDER BY clause)
2. When there is a Reserved Word used as the lookup table or column name. You must enclose the reserved word in quotes in order to let the integration service query the relational table successfully. There is another way to handle reserved words in informatica. More details on this in the below post.
How to handle table or column names with reserved words in Informatica?
3. When you wish to use mapping parameters and variables in the lookup query. You can use any parameter or variable, that you can define in a parameter file, in the SQL override statement. You can also use a parameter or variable as the SQL query for e.g. session parameter $ParamMyLkpOverride can be used as a substitute of lookup query override. You can assign the query from a parameter file. You cannot validate the query override while using parameters or variables as the mapping designer cannot expand them, but it works fine when the session is executed.
4. When you wish to use a WHERE clause to filter out unwanted rows from lookup cache. You should use a filter transformation before the lookup with the same filter clause as the lookup WHERE clause, so that lookup is fed with data that satisfies the filter condition of the lookup.
5. When you wish to modify the lookup data before the integration services caches the rows. For e.g. using conversion functions for dates, multiplying columns etc.
Following guidelines must be observed while overriding the lookup query:-
1. Generate the default query and then modify it. This ensures none of the ports in the lookup are missed.
2. Use same SQL override for each lookup transformation that uses a shared cache.
3. While overriding the order by clause ensure the order of the columns is same as in the lookup ports and also suppress the default Order By clause by using comments notation. Otherwise the session will fail.
Hope this helps you in configuring your lookup overrides. For any more information leave a comment and I will respond.