SQL Query Overwrite in Source Qualifier – Informatica

Written by
  • Updated
  • Posted in Programming
  • 3 mins read


One of the properties of supply qualifier transformation is “SQL Query” which can be utilized to overwrite the default question with our custom-made question. We can generate SQL queries just for relational sources. For flat recordsdata, all of the properties of supply qualifier transformation will likely be disabled state.

Here we are going to see the best way to generate the SQL question and the errors that we’ll get whereas producing the SQL question.

Error When Generating SQL question:

The most frequent error that we’ll get is “Cannot generate question as a result of there are not any legitimate fields projected from the Source Qualifier”.

First we are going to see simulate this error after which we are going to see the best way to keep away from this. Follow the beneath steps for simulating and fixing error:

  • Create a brand new mapping and drag the relational supply into it. For instance drag the purchasers supply definition into the mapping.
  • Do not join the supply qualifier transformation to any of different transformations or goal.
  • Edit the supply qualifier and go to the properties tab after which open the SQL Query Editor.
  • Enter the ODBC information supply identify, consumer identify, password after which click on on Generate SQL.
  • Now we are going to get the error whereas producing the SQL question.
  • Informatica produces this error as a result of the supply qualifier transformation ports usually are not related to every other transformations or goal. Informatica simply is aware of the construction of the supply. However it doesn’t know what columns to be learn from supply desk. It will know solely when the supply qualifier is related to downstream transformations or goal.
  • To keep away from this error, join the supply qualifier transformation to downstream transformation or goal.

Generating the SQL Query in Source Qualifier:

To clarify this I’m taking the purchasers desk because the supply. The supply construction appears to be like as beneath

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

Follow the beneath steps to generate the SQL question in supply qualifier transformation.

  • Create a brand new mapping and drag the purchasers relational supply into the mapping.
  • Now join the supply qualifier transformation to every other transformation or goal. Here I’ve related the SQ to expression transformation. This is proven in the beneath picture.
  • Edit the supply qualifier transformation, go to the properties tab after which open the editor of SQL question.
  • Enter the username, password, information supply identify and click on on Generate SQL question. Now the SQL question will likely be generated. This is proven in the beneath picture.

The SQL question generated is

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

Now we are going to do a small change to know extra in regards to the “Generating SQL question”. Remove the hyperlink (connection) between Name port of supply qualifier and expression transformation.

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

The SQL question generated in this case is

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

The Name column is lacking in the generated question. This signifies that regardless of the ports related from Source Qualifier transformation to different downstream transformations or goal will likely be included in the SQL question and browse from the database desk.

Leave a Reply