- 21 Nov 2023
- 1 minute read
- Print
- DarkLight
- PDF
Custom SQL Hybrid Queries
- Updated 21 Nov 2023
- 1 minute read
- Print
- DarkLight
- PDF
The query system supports hybrid queries that pair the advanced capabilities of custom SQL with the intuitive, straightforward query system filters that can be edited by any end-user. These hybrid queries enable cross-tab reports and other advanced queries to be built while allowing end-users to change the population as needed.
Utilizing Hybrid Capability
To utilize this hybrid capability, when building your custom SQL query click Edit Properties, and select the Hybrid Base on which you wish to filter, and Save. The Hybrid Base chosen should match on the primary table of the query.
If you are seeking to filter by prospect- or person-scoped information, for example, you would select Prospects.
A "Hybrid Filters" section will appear below the query. While the setting makes the filters available, adding hybrid filters do not alone filter your custom SQL.
Example
For example, consider the following simple query:
select p.[first], p.[last], p.[email], r.[name] as [round]
from [application] a
inner join [person] p on (p.[id] = a.[person])
inner join [lookup.round] r on (r.[id] = a.[round])
where (p.[first] = 'John')
This query will pull all applicants who have the first name "John". If we want the additional hybrid filters to apply to the predefined query, we can update our custom SQL to include the following addition to the where clause:
select p.[first], p.[last], p.[email], r.[name] as [round]
from [application] a
inner join [person] p on (p.[id] = a.[person])
inner join [lookup.round] r on (r.[id] = a.[round])
where (p.[first] = 'John') and (p.[id] in (select [id] from @ids))
The @ids is a temporary table that consists of a single column containing unique identifiers based on the Hybrid Base selected (e.g. Applications: a.[id]; Prospects: p.[id], etc.) and the hybrid filters selected by the user.