SQL Override​ in Informatica​

by kvt - Comment is Closed

We have an option to customize the default query provided by the transformations and enter custom SQL query by using SQL override. The most common transformations which support the SQL overrides are Source Qualifier & Lookup transformations.

If a table name or column name in the lookup query contains a reserved word, enclose the reserved word in quotes.

When you override the default SQL query for a session configured for pushdown optimization, the Integration Service creates a view to represent the SQL override. It then runs an SQL query against this view to push the transformation logic to the database.

Source Qualifier SQL Override

  • The Source Qualifier transformation provides the SQL Query option to override the default query.
  • We can use a parameter or variable as the SQL query or include parameters and variables within the query.
  • The SELECT statement must list the port names in the order in which they appear in the transformation.
  • The number of columns in the SELECT statement in the query must match the number of ports in the Source Qualifier transformation.

Lookup​ ​SQL override

  • The default lookup query contains the SELECT statement includes all the lookup ports in the mapping and the ORDER BY clause which orders the columns in the same order they appear in the Lookup transformation.
  • You can override the lookup SQL query for relational lookups.
  • The​ ​default​ ​ORDER BY clause contains all lookup condition ports. To increase performance, you can suppress the default ORDER BY clause and enter an override ORDER BY with fewer columns.
  • The Integration Service always generates an ORDER BY clause, even if you enter one in the override. We​ ​cannot view this when you generate the default SQL but can view in the session log.
  • Generate the default query, and then configure the override. This ensures that all the lookup/output ports are included in the query. If you add or subtract ports from the SELECT statement, the session fails.
  • Add a source lookup filter to filter the rows that are added to the lookup cache. This ensures that the Integration Service inserts rows in the dynamic cache and target table that match the WHERE clause.
  • If multiple Lookup transformations share a lookup cache, use the same lookup SQL override for each Lookup transformation​.​
  • The ORDER BY clause must contain the condition ports in the same order they appear in the Lookup condition.
  • If you override the ORDER BY clause, use the comment notation to suppress the ORDER BY clause that the Lookup transformation generates.
  • If you use pushdown optimization, you cannot override the ORDER BY clause or suppress the generated ORDER BY clause with comment notation.
  • To override the lookup query for an uncached lookup, choose to return any value when the Integration Service finds multiple matches.
  • You cannot add or delete any columns from the default SQL statement.
  • The SQL override cannot contain parameters or variables.

Related Posts: