Custom SQL Queries
Applicable only to SQL-based connectors, a data source using a connector that supports custom SQL queries can use an SQL query to select fields from the table. The custom SQL statement can be specified on the Tables/Indices Tab of the data source configuration wizard after selecting the Custom SQL button.
Custom SQL queries are a powerful tool for performing complex data queries. However, be careful when creating custom SQL queries because it is easy to define a heavy query or a query that may overwhelm your database. Use this feature carefully.
In SQL-based sources, Composer typically wraps the query with select * from. For example, suppose the original query is this:
select count(*), someField from myCollection GROUP By someField
The resulting query that Composer uses is this:
select * from (select count(*), someField from myCollection GROUP By someField)
Support for this feature by Composer connectors is shown in the following table.
Key: Y - Supported; N - Not Supported; N/A - not applicable
|Apache Phoenix Query Server (QS)||Y|
|Microsoft SQL Server||Y|
|Real Time Sales||N|