SQL Query Overwrite in Source Qulifier

One of the properties of source qualifier transformation is "SQL Query" which can be used to overwrite the default query with our customized query. We can generate SQL queries only for relational sources. For flat files, all the properties of source qualifier transformation will be disabled state.

Here we will see how to generate the SQL query and the errors that we will get while generating the SQL query.

Error When Generating SQL query:

The most frequent error that we will get is "Cannot generate query because there are no valid fields projected from the Source Qualifier".

First we will see simulate this error and then we will see how to avoid this. Follow the below steps for simulating and fixing error:

• Create a new mapping and drag the relational source into it. For example drag the customers source definition into the mapping.

• Do not connect the source qualifier transformation to any of other transformations or target.
• Edit the source qualifier and go to the properties tab and then open the SQL Query Editor.
• Enter the ODBC data source name, user name, password and then click on Generate SQL.
• Now we will get the error while generating the SQL query.

• Informatica produces this error because the source qualifier transformation ports are not connected to any other transformations or target. Informatica just knows the structure of the source. However it doesn't know what columns to be read from source table. It will know only when the source qualifier is connected to downstream transformations or target.
• To avoid this error, connect the source qualifier transformation to downstream transformation or target.

Generating the SQL Query in Source Qualifier:

To explain this I am taking the customers table as the source. The source structure looks as below

Create table Customers
(
Customer_Id Number,
Name        Varchar2(30),
Email_Id    Varchar2(30),
Phone       Number
)

Follow the below steps to generate the SQL query in source qualifier transformation.

• Create a new mapping and drag the customers relational source into the mapping.
• Now connect the source qualifier transformation to any other transformation or target. Here I have connected the SQ to expression transformation. This is shown in the below image.

• Edit the source qualifier transformation, go to the properties tab and then open the editor of SQL query.
• Enter the username, password, data source name and click on Generate SQL query. Now the SQL query will be generated. This is shown in the below image.

The SQL query generated is

SELECT Customers.Customer_Id,
        Customers.Name,
 Customers.Email_Id,
 Customers.Phone
FROM Customers

Now we will do a small change to understand more about the "Generating SQL query". Remove the link (connection) between Name port of source qualifier and expression transformation.

Repeat the above steps to generate the SQL query and observe what SQL query will be generated.

The SQL query generated in this case is

SELECT Customers.Customer_Id,
 Customers.Email_Id,
 Customers.Phone
FROM Customers

The Name column is missing in the generated query. This means that whatever the ports connected from Source Qualifier transformation to other downstream transformations or target will be included in the SQL query and read from the database table.