Connecting through Custom SQL Query
What is a Custom Query Table ?
You can also connect to data source of certain data connections through custom SQL query. This feature allows you to define the data source with the manually written queries instead of manual drag and drop of tables. This is possible not only for database-type Connections, but also for other types.
Language
When connecting directly to a database, the language of the Custom Query must be that of the database. When connecting to bufferized data, the Custom Query must be typed in the language of the buffer database. see Buffer
Important
When changing the underlying buffer database technology (for example when switching from SQL to SQLite), the Custom Query Table will probably need to be updated accordingly, since there are some differences in SQL language used.
Creating a Custom Query Table
Open the Data Source where you need to add a Custom Query. From the Connection schema, click on “Add Custom Query”.
Write the query in the correct SQL language (see above). For bufferized Connections, you can use the table names as listed in the schema.
Click OK to create the Custom Query Table. Fix any SQL error if necessary.
Now the Custom Query Table is created in the Data Source currently opened. It can be used like a normal table from the same Connection and for example can be used in a Join.
Updating a Custom Query Table
Click on the cog icon to view and update the Custom Query.
Important
Be careful that any change in the schema (column names and types) could impact the items that use this Custom Query Table, such as Widgets or Expressions. Changing the schema of the query could require to re-do some or all of the related data binding.
Click OK to update the Custom Query Table. Fix any SQL error if necessary.
Using Parameters
Parameters can be used to parametrize a Custom Query : the Custom Query changes dynamically.
Syntax
${parametername} will be replaced in-place by the values of “parametername”.
Example :
SELECT *
FROM MyTable
WHERE MyColumn = ${parametername}
will get replaced at runtime by :
SELECT *
FROM MyTable
WHERE MyColumn = parametervalue
Parameters that return multiple values will return them separated by commas, so that you can write :
SELECT *
FROM MyTable
WHERE MyColumn IN( ${multiparameter} )
and it will get replaced at runtime by :
SELECT *
FROM MyTable
WHERE MyColumn IN( Value1,Value2,Value3 )
Note
In the Designer, the Custom Query will be executed with the default parameter values. This may cause unwanted syntax errors if the default value is empty for example. Setting a default value to the parameter fixes the issue.