Informatica Power Center Integration Service allows us to override the default query generated by it while running a session. Power Center Integration Service generates the query when querying the relational Soures and Lookups. In this section I discuss about SQL Override in SQ.
Relational Sources:
Before override the the default query we must first understand the Default Query.For relational sources, the Integration Service 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 Integration Service reads only the columns that are connected to another transformation.
Ex: If the SQ contains 10 columns out of which only 3 are connected to next transformation then the default SQL Query generated contains only those 3 columns in it.
If any table name or column name contains a database reserved word, you can create and maintain a file,
reswords.txt, containing reserved words. When the Integration Service initializes a session, it searches for
reswords.txt in the Integration Service installation directory. If the file exists, the Integration Service places quotes around matching reserved words when it executes SQL against the database. If you override the SQL, you must enclose any reserved word in quotes.
When generating the default query, the Designer delimits table and field names containing the following characters with double quotes:
/ + - = ~ ` ! % ^ & * ( ) [ ] { } ' ; ? , < > \ | <space>
You must connect the columns in the Source Qualifier transformation to another transformation or target before you can generate the default query.
Viewing the Default Query
You can view the default query in the Source Qualifier transformation.
To view the default query:
1. From the Properties tab, select SQL Query.
2. Click Generate SQL.
3. Click Cancel to exit.
To view the default query:
1. From the Properties tab, select SQL Query.
2. Click Generate SQL.
3. Click Cancel to exit.
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.
Points to be noted while overriding the default query:
1. Do not change the list of selected ports or the order in which they appear in the default query. This list must match the connected transformation output ports.
2. When you edit transformation properties (User-Defined Join, Source Filter, Number of Sorted Ports, and Select Distinct) the Source Qualifier transformation includes these settings in the default query.
3. However, if you enter an SQL query, the Integration Service uses only the defined SQL statement and ignores the properties set in the Source Qualifier transformation.
4. When you override the default SQL query, you must enclose all database reserved words in quotes.
Overriding the Default Join:
You might need to override the default join under the following circumstances:
1.Columns do not have a primary key-foreign key relationship.
2. The datatypes of columns used for the join do not match.
1.Columns do not have a primary key-foreign key relationship.
2. The datatypes of columns used for the join do not match.
3. You want to specify a different type of join, such as an outer join
When joining tables you can perform in two ways:
1. Import both the tables to be joined and drag into the mapping area:
If foreign key -primary key relationship exists between them in database itself then a link indicating FK-PK relation is shown in mapping.When you generate the query a default join condition is added.
2. the mqapping contains only one Source definition and you can override the default query with the join query you want.
Note:
1. Add the necessary columns in the SQ by clicking on "create new port" icon. and name chose the data types for columns accordingly.
2. connect these new ports with the ports of same datatype from source definition .
3. Edit the default query to reflect the new column names.(The generated query contains the column names from source to which you connected the new ports in place of new port names.)
You can see the same in below figure .
you can validate the override query by providing the parameters required in the "connect to database" section .
Hope this helps.
