Can we dynamically create a SQL statement in a profile which has to be used to a connector get operation. I mean the whole of SQL statement.
No, You cannot create SQL statements dynamically.
You can pass values dynamically if needed to the parameters.
As Srinivas mentioned it is not possible to have dynamic SQL statement in Database connector. However, in some cases it becomes really difficult to have multiple database connector shapes for executing sql's based on conditions.
The best method that I found was to create a Stored Procedure and pass SQL statement dynamically as parameter to the stored procedure. Now, this will work if the fields in the database profile matches the output of stored procedure.
In order to get past the fixed number of output fields in database profile, I created only one field called result.
The output of stored procedure was in xml format (eg: select top 1 * from employee for xml).
So in this way dynamic SQL was executed with help of stored procedure which returned multiple column data in xml format. With latest version of SQL server 2016 we can get json out which would be very helpful.
This thread may offer some inspiration: How to change oracle schema name dynamically whene we query from Oracle DB?
Retrieving data ...