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:
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.
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.