Query Syntax References

The Holistics Query Syntax gives you more flexibility in designing your report queries. It is used to provide both you and your users with the flexibility to customize your report data. The Holistics query syntax is most commonly used in report filters when setting up report queries.

The example syntax shown here is based on Postgres SQL.

Holistics provides you with several tools for manipulating your queries and data output. These include embedded tags, shared templates and a pivot table option. This page will explain the use of these four query tools:

SQL Query Variables: How to include your filter variables in your query.
SQL Query Conditions: Using if conditions inside your SQL query.
SQL Query Templates: Setting up query templates to minimize duplication.
Quick Pivots: Pivoting your result data.

SQL Query Variables

Values

Holistic SQL queries accept multiple types of variables which can be included into queries through the use of filters. Users can select values to be passed into filters, which then work as a part of the SQL query to extract data from the database. Users can easily select input variables without having to touch the original SQL script themselves. Several different filter types are available: filters can be set up to allow date values, text values, lists and selections from dropdown menus. To set up a filter, please see Report Filters.

To add a filter into your SQL syntax, simply add your filter “Variable Name” into the query using double brackets:

{{filter_name}}

The double square brackets wrapper [[ ]] is also needed around the entire condition to ensure no values are passed into the condition from anywhere else, and no syntax errors occur.

WHERE [[ U.created_at::date >= {{signup_from}} ]]

Following Postgres SQL syntax, the variables can be manipulated in several ways. A wildcard can be added to the filter value by including '%' on each side of the filter name, separated by ||:

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

The case of the value can be changed, for example, by adding LOWER() around the variable to change all letters into lowercase. One can also concatenate filter variables using the CONCAT() tag. Taken name = “Bob”, this value

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

would become '%bob%', where % represents any letters around bob. Using the argument above, we can use the LIKE statement to look for any realname in table U, with the the letter combination bob in it:

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

Other examples of filter values within a SQL query :

  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}} ]]

Using the example above, if the user was to choose filter values for "Sign Up From", "Sign Up To", and "Country", the above query would become:

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 input is added into a filter, the condition will simply appear as (1=1), however, once your filters are added to report, the filter fields will appear in the report even as non-functional. Make sure you use the filter variables inside your query. Read more about no input values on Empty Input Value.
Single quotes are automatically added around filter values. For example {{signup_from}} will become '2016-01-01'. To remove these quotes, the |noquote tag can be used. Read more about noquote on Noquote.
If the filter is a multi-select, the values will be inserted into the query with single quotes and separated by commas. For example, {{country}} would become 'sg','vn'. In such case, if you are using the IN condition, remember to wrap the filter variable around round brackets such as here: country IN ({{country}})

Noquote

In some cases you might want to include a filter variable into your query without its quotation marks. This can be especially useful in cases where the value needs to be an integer instead of a string, or where the variable needs to be a part of a function name. In these cases the |noquote tag is used.

Example 1: Consider a report called "Users with Minimum X Bookings" that uses a filter "Minimum Bookings" with number values. Here we want min_bookings to appear as a 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 included from a filter variable.

select 
  id as "ID",
  product_name as "Name"
from products
where [[ product_type = 'glass-{{name|noquote}}' ]]

By setting name as “jar”, the input variable would become 'glass-jar' which would produce a list of glass jar product, while without the noquote tag, the input would be 'glass-'jar'', which would generate a syntax error.

Empty INPUT VALUE

It is recommended to add double brackets [[ ]] around query parameters with filter variables. What these do, is exclude the specific WHERE clause in the event of a zero input. An argument like this:

AND [[ U.country_code IN ( {{country}} ) ]]

will be replaced by AND (1=1).

and the country code filter will be excluded from the query, resulting in all countries being shown. If there are no values passed into the filter, the argument inside the double brackets will convert into a simple true statement (1=1).

When you load a new report, the default value for the dropdown filter will be All . The only way to change this will be to check the Hide All checkbox in dropdown settings, in which case no value will be passed into your SQL query.

SQL Query Conditions

If-Else

The Holistic query conditions allow you to include If conditions into your SQL queries. The If condition allows you to conditionally render text blocks, include nested conditions and include else statements.

The basic syntax includes an opening if tag:

{{#if expression}}

an optional else condition:

{{#else}}

and the end tag:

{{#endif}}

In these examples we can see if statements rendering SQL text blocks and the use of two if statements together imposing two conditions at once:


{{#if channel == 'youtube'}} BLOCK 1 {{#else}} BLOCK 2 {{#endif}}
{{#if channel != 'youtube'}} SQL TEXT BLOCK {{#endif}}

{{#if source}}
    {{#if partner}}
       both source and partner
    {{#else}}
       only source, no partner
    {{#endif}}
{{#endif}}

Expression

The if statement requires an expression in order to be fulfilled. These expressions are included inside the {{#if }} tag and are generic boolean expressions. Expressions and, or, ==, !=, not are supported.

The expressions are included inside the {{#if }} tag:

{{#if EXPRESSION}}some code{{#endif}}

As an example, this if statement only shows VALUE if the source value equals 'direct':

{{#if source == 'direct'}} VALUE {{#endif}}

This can be expanded to include and and or statements, where the condition is only met when both expressions are true (and) or if at least one expression is true (or).

`{{#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}}`

Benefits/Use Case

The if condition is a widely used statement in several programming languages, and for many, the logic of an if statement can be easier to work with than native SQL syntax.
The if condition can be used to confine your query based on the variables. Here we see the second section of the WITH R AS query being blocked if the channel name is not 'youtube'. For more difficult queries this can significantly decrease complexity and improve processing efficiency.

WITH R AS (
  select date_d, SUM(cnt)
    from video_plays
    where [[ source = {{source}} ]
      and [[ partner = {{partner}} ]]
    group by date_d
    order by 1 desc
    {{#if channel == 'youtube'}}
    UNION
    select date_d, sum(cnt)
    from video_plays_youtube
    where [[ source = {{source}} ]]
    and [[ partner = {{partner}} ]]
    group by date_d
    order by 1 desc
    {{#endif}}
)

select
 date_d AS "Date",
 SUM(video_starts) AS "Video Starts"
FROM R
GROUP by 1

The if condition also makes it possible to easily preform multiple actions based on a value.

{{#if active}}
     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}

With SQL you would either need to repeat yourself or apply complicated workarounds.

 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 allow you to define reusable snippets of SQL components to be used across different reports. For more details please refer to this document: Query Templates

Quick Pivots

Quick Pivot allows you to pre-transform something like this:

into this...

You can then apply a visualization on top of the transformed data. Learn More About Quick Pivot

{{ section.section }}