Query Syntax

Holistics Query Syntax gives you more flexibility in designing your report queries. On top of the base SQL syntax supported by your database, Holistics's syntax adds a few convenient functionalities, which are:

SQL Query Variables: Easily parameterize your queries
SQL Query Conditions: Using if-else clauses to change your SQL conditionally
SQL Query Templates: Setting up query templates and improve code reusability

info

The examples shown here are based on Postgres SQL or BigQuery's StandardSQL syntax.

SQL Query Variables#

Variables can be used in Reports's queries..

How to use#

To add a filter into your report's query, simply refer to the filter's Variable Name using double curly brackets:

The variable can be inserted in many different places in the queries. Values passed to the variable are wrapped in single quote to turn them into strings.

Use "noquote" tag for raw value#

In some cases, you may want to use the raw values of the filter instead of their string representation. To do so, simply a |noquote tag after the variable name:

Example 1: In a report called "Users with Minimum X Bookings" that uses a filter "Minimum Bookings" with number values, we would want to use the raw min_bookings number instead of a string:

SELECT * FROM users
WHERE [[ num_bookings >= {{min_bookings|noquote}} ]]

Example 2: Here we want to only find products starting with the word “glass-”. The end of the product name can be inserted to the final string with a filter variable:

SELECT 
  id as "ID",
  product_name as "Name"
FROM products
WHERE [[ product_type = 'glass-{{name|noquote}}' ]]

When you select “jar” in the Name filter, the final string will be 'glass-jar', while without the noquote tag, the result would be 'glass-'jar'', which is invalid.

Where to use filter variable#

In WHERE clause#

If a variable is used in a WHERE clause, double square brackets [[ ]] is also needed around the entire condition to ensure even when there is no value passed to the variable, the final query is still valid:

WHERE [[ U.city_name in ( {{city}} ) ]]

For example, if you select 'Tokyo' and 'Manila' in the City dropdown filter, the query above will be translated to:

WHERE U.city_name in ('Tokyo', 'Manila')

If you do not select any value for City variable, the query will be translated to:

WHERE (1 = 1)

The variable can be used to modify a string:

'%' || {{name}} || '%'

Because filter values are, by default, of string type, it can be concatenated with other strings.

CONCAT('%', LOWER({{name}}), '%')

For example, you have a Name filter and selected Bob as value. The function above will return '%bob%', which is can be used in a LIKE statement:

[[ LOWER(U.realname) LIKE CONCAT('%', LOWER({{name}}), '%') ]]

Let's take a look at more complicated condition clauses:

  SELECT
    id, name, email, country_name, num_bookings
  FROM users U
  WHERE [[ U.created_at::date >= {{signup_from}} ]]
    AND [[ U.created_at::date <= {{signup_to}} ]]
    AND [[ U.country_code IN ( {{country}} ) ]]
    AND [[ U.name ILIKE '%' || {{name}} || '%' ]]
    AND [[ U.email ILIKE '%' || {{email}} || '%' ]]
    AND [[ U.num_bookings = {{num_bookings|noquote}} ]]

If the user selected filter values for "Sign Up From", "Sign Up To", and "Country", and leave Name, Email and Booking Number blank, this is the resulted query:

SELECT
  id, name, email, country_name, num_bookings
FROM users U
WHERE U.created_at::date >= '2016-03-01'
  AND U.created_at::date <= '2016-03-31'
  AND U.country_code IN ( 'sg', 'vn' )
  AND 1 = 1
  AND 1 = 1
  AND 1 = 1

Notes

If no value is selected for the filter, the condition will be translated to (1 = 1)
If the filter allows multi-select, the values will be inserted into the query as a comma-separated list. For example, {{country}} will become 'sg','vn'. You will need to wrap the variable in round brackets and use IN operator like this: country IN ( {{country}} )

In other parts of the query#

Variable and noquote tag can be used to pass reserved keywords to the query. For example, we have a "Time Period" filter with the following values:

The keywords like month, quarter, year must be passed without quote into the query, so we use noquote tag:

 SELECT
      date_trunc(deal_month, {{aggregation_period|noquote}}) as deal_time
      , FROM(deal_value) as total_deal_value
  from sales.deals
  group by 1

If we select "Monthly" option, the query will be:

 SELECT
      date_trunc(deal_month, month) as deal_time
      , FROM(deal_value) as total_deal_value
  from sales.deals
  group by 1

SQL Query Conditions#

Basic syntax#

The Query Conditions syntax allows you to use filters to conditionally change your query.

The basic syntax includes:

An opening if tag: {{#if ... }} followed by a SQL or a text block
An optional else tag: {{#else}} followed by a SQL or a text block
The end tag: {{#endif}}

The query can have the following forms:

{{#if variable == 'value1'}} 
    SQL/TEXT BLOCK 1 
{{#else}} 
    SQL/TEXT BLOCK 2 
{{#endif}}

---

{{#if variable1 == 'value1'}}
    {{#if variable2 == 'value2'}}
       SQL/TEXT BLOCK 1
    {{#else}}
       SQL/TEXT BLOCK 2
    {{#endif}}
{{#endif}}

In other words, you can use the syntax to conditionally run a totally new SQL, or just alter a part of your original SQL query.

Operators#

In the opening {{#if ... }} tag, generic boolean operators are supported including: and, or, ==, !=, not.

For example:

{{#if source == 'direct'}} select * from direct_users {{#endif}}

The conditional statement can be even more complex with and, or and not:

{{#if source == 'direct' and partner != 'youtube'}} VALUE {{#endif}}

{{#if report_type == '270co' or report_type == '268co'}} VALUE {{#endif}}

{{#if source == 'direct' and partner != 'youtube'}} VALUE {{#endif}}

{{#if not(source == 'direct' and partner == 'youtube')}} VALUE {{#endif}}

Use Cases#

Conditional branching is a must-have in the most common programming languages, but unfortunately, it is not available in base SQL.

As shared above, you can now conditionally run a totally new SQL, or just alter a part of your original SQL query. This gives Holistics users even more power to control and be creative with their report logic.

Example 1#

In the following example, we use the condition syntax to add a UNION to the base query if the Chanel filter has value 'youtube':

with R as (
  select date_d, sum(cnt) as video_starts 
  from video_plays
  where [[ source = {{source}} ]] and [[ partner = {{partner}} ]]
  group by 1 order by 1 desc

  {{#if channel == 'youtube'}}
  union
  select date_d, sum(cnt) as video_starts 
  from video_plays_youtube
  where [[ source = {{source}} ]] and [[ partner = {{partner}} ]]
  group by 1 order by 1 desc
  {{#endif}}
)

select date_d as "Date",  sum(video_starts) as "Video Starts"
from R
group by 1

Example 2#

Here we can conditionally modify the SELECT statement itself:

SELECT 
{{#if active = 1}}
     last_online_date as "Date",
     club_name as "Club",
     'Active' as "Type"
 {{#else}}
     sign_up_date as "Date",
     'No club' as "Club",
     'Inactive' as "Type"
 {{#endif}
 

This is equal to using three CASE ... WHEN... statements for the three columns:

select
 case
   when active = 1 then last_online_date
   else sign_up_date
 end as "Date",
case
   when active = 1 then club_name
   else 'No club'
 end as "Club",
 case
   when active = 1 then 'Active'
   else 'Inactive'
 end as "Type"

Query Templates#

Query Templates are SQL snippets that can be applied and reused across different reports. For more details please refer to this page: SQL Query Templates.

The basic syntax to refer to a query template is {{ @template_name(template_var) }} where template_var is the name of the variable used in the template.

For example, we have the following snippet, and the syntax to refer to it in a query is:

The column can vary in different querying situation.

In a certain scenario, the full query can be:

SELECT
  J.id as "ID",
  J.title as "Title"
  {{ @job_status('J.status') }} AS "Status"
FROM jobs J