SQL override in Informatica

For relational sources, the PowerCenter Server generates a query for each Source Qualifier transformation when it runs a session. The default query is a SELECT statement for each source column used in the mapping. In other words, the PowerCenter Server reads only the columns that are conneted to another transformation. 

Although there are many columns in the source definition, only three columns are connected to another transformation. In this case, the PowerCenter Server generates a default query that selects only those three columns: 

SELECT CUSTOMERS.CUSTOMER_ID, CUSTOMERS.COMPANY, CUSTOMERS.FIRST_NAME 

FROM CUSTOMERS

When generating the default query, the Designer delimits table and field names containing the slash character (/) with double quotes. 

Viewing the Default Query 

You can view the default query in the Source Qualifier transformation. 

To view the default query: 

From the Properties tab, select SQL Query. 

The SQL Editor displays. 

Click Generate SQL. 

The SQL Editor displays the default query the PowerCenter Server uses to select source data. 

Click Cancel to exit. 

Note: If you do not cancel the SQL query, the PowerCenter Server overrides the default query with the custom SQL query.

Do not connect to the source database. You only connect to the source database when you enter an SQL query that overrides the default query. 

Tip: You must connect the columns in the Source Qualifier transformation to another transformation or target before you can generate the default query.

Overriding the Default Query 

You can alter or override the default query in the Source Qualifier transformation by changing the default settings of the transformation properties. Do not change the list of selected ports or the order in which they appear in the query. This list must match the connected transformation output ports. 

When you edit transformation properties, the Source Qualifier transformation includes these settings in the default query. However, if you enter an SQL query, the PowerCenter Server uses only the defined SQL statement. The SQL Query overrides the User-Defined Join, Source Filter, Number of Sorted Ports, and Select Distinct settings in the Source Qualifier transformation.