Close

Custom SQL

Omniata allows users to write their own SQL with MySQL syntax, which is useful when creating complex queries that include joins, subselects, unions, or other advanced functions. The custom SQL mode is accessed through the Widget Builder by selecting SQL Mode. A basic example would be:

SELECT * FROM <table_name>

The table name is the SQL table name of the Reporting Table you will be using. To find a table name, navigate to Data Model > Tables and look at the column 'Table Name'. Please note that all Reporting Tables have a machine name as well, which is an internal identifier for Omniata. This can be set to be the same as the SQL table name, but by default it will not be. Writing a query that uses the machine name instead of the SQL table name will result in an error.

The columns in the SELECT statement are the System ID names of the Data Fields you wish to use.

Please note that there may be display issues when using custom SQL that generate null or empty string ('') values in the result. If a SQL query is generating values like this, it's best to use a CASE statement to force the null or blank values to be numeric. An example might look like this:

SUM(CASE WHEN ((result = '') OR (result IS NULL)) THEN 1 ELSE 0 END) AS `Total Result`

Using Filters with a Custom SQL Widget

You can use dashboard filters to modify the data set returned by a Custom SQL widget by including the following:

WHERE $WHERE
WHERE $WHERE(<table_name>,<table alias>)
HAVING $HAVING
HAVING $HAVING(<table_name>,<table alias>)

If, due to sub-queries or joins, the table name does not match the machine-name of the reporting table, then it can be specifically changed using the alternative syntax (see lines 2 and 4 above). Multiple $WHERE and $HAVING tokens are supported. The columns in the select statement must match the machine names of the fields being used in the filter. For example, Activity Date is om_activity_date and the column cannot be aliased as anything else.

SELECT
    activity_date,
    users,
    count(distinct game) as games
FROM
(    SELECT
        activity_date,
        game,
        SUM(users) AS users
    FROM inner_table #assume machine name is "inner_table_mn"
    GROUP BY 1,2
) AS outer_table
WHERE $WHERE(inner_table_mn,outer_table)
GROUP BY 1,2
HAVING $HAVING(inner_table_mn,outer_table)

Using Variables and Running Totals

The creation and use of variables follows the same structure as that of MySQL. A MySQL variable is a column field that is created in a query and does not exist in the table. A variable is denoted using the @ symbol with a name to accompany (i.e. @x or @y)

Variables can be used for setting a constant number for use in formulas. A variable works by using the @ symbol and assigning it a name:

SELECT( @y:=0 ) v

The variable here is named y and referenced as @y. We initially set the variable to 0, so any references to @y at this point would result in 0. If we want to update the variable to add a + 1 for each row, we could use the following formula:

@y:=@y+1 AS 'variable'

This will result in a column where @y increments by 1 with each row:

Date Variable
2014-05-01 1
2014-05-02 2
2014-05-03 3

You can also use variables in mySQL to create a running sum, reference a value on a given date or action, or count rows in the table. For example, to create a running total the following statement can be used.

SELECT
  b.om_activity_date,
  @x:= b.om_total_revenue + @x
FROM
(SELECT @x:=0) a,
(SELECT
  om_activity_date,
  om_total_revenue
FROM table
GROUP BY 1
ORDER BY 1 DESC) as b
GROUP BY 1
ORDER BY 1 DESC

Using functions

Functions can be used for building widgets. Supported functions include the type: control, numeric, date, time, text search, and cast. Here are some examples of supported functions:

SELECT 
  IFNULL(<expr>,0) AS field1,
  COUNT(DISTINCT om_uid) AS field2,
  SUM(IF(<expr>,<true>,<false>)) AS field3
FROM
  <table>

Joining Reporting Tables

Joins can be used to join two reporting tables on a shared column of data, such as om_activity_date. Here is a basic example of a join clause:

SELECT
  a.date,
  a.users,
  b.users
FROM
  (SELECT
    date,
    users
  FROM a ) a
JOIN
  (SELECT
    date,
    users
  FROM b) b
WHERE a.date = b.date

When joining two different tables the filters may need to point to different field/table names. To tell the filters to use a specific table name we can employ the $WHERE and $HAVING aliases.

SELECT sm.`om_activity_date`, sm.`om_new_users`, cm.`om_new_users` AS `cm_new_users`
FROM (SELECT date(a.activity_date) AS `om_activity_date`, SUM(a.`new_users`) AS `om_new_users` 
    FROM `om_day_gmet_eng` a
    WHERE (a.`project_name` = 'My Game') AND $WHERE(om_day_gmet_eng, a) 
    GROUP BY date(a.activity_date)) AS `sm`
LEFT JOIN
    (SELECT `om_activity_date` AS `om_activity_date`, SUM(IF(om_retained_days = 0, om_users, 0)) AS `om_new_users` 
    FROM `md_mnt1_bb7e` 
    WHERE $WHERE 
    GROUP BY `om_activity_date`) AS `cm` ON sm.`om_activity_date` = cm.`om_activity_date`
ORDER BY sm.`om_activity_date` ASC

This article was last updated on October 22, 2015 21:22. If you didn't find your answer here, search for another article or contact our support to get in touch.

Contact Sales

Add Phone Number