For relational database sources, the PowerCenter Informatca Server generates a query for each Source Qualifier transformation when it runs a mapping.
The default query is a SELECT statement for each source column used in the mapping. In a nutshell, the PowerCenter Server reads only the columns that are connected to another transformation.
Even though there are many columns in the source definition, if only four columns are connected to another transformation. In this case, the PowerCenter Server generates a default query that selects only those four columns.
SELECT ORDERS.ORDER_ID, ORDERS.PRODUCT, ORDERS.ADDRESS, ORDERS.QUANTITY FROM ORDERS;
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.
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.
If you do not cancel the SQL query, the PowerCenter Server overrides the default query with the custom SQL query.
Note: 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.
