SQL Expression Field
Contents
Introduction
A SQL Expression Field is a calculated field that perform its calculation at the data source.
This advanced feature creates a calculation based on other table fields using a SQL language expression. This expression is passed through to the database and is calculated at the database.
You can define a SQL Expression Field for any table in a relational (SQL) data sources. This includes the "explore" data source where data stored in Explore Analytics is kept.
Defining a SQL expression requires knowledge of the database SQL syntax. For the "explore" data source, the database is MySQL.
Only an administrator (tenant admin) can create or modify a SQL Expression Field.
When Should a SQL Expression Field be used?
- When you need to calculate or convert data at the data source. For example, if you have a date stored in a character field and you want to convert it to a date/time field so that it can be grouped by month for example.
- When you need to make line-item calculations and later aggregate them. For example, if you have invoice lines or order lines with price, quantity, and discount, you can calculate the extended and discounted price of the line and then use it in a pivot or chart to summarize that value.
- When you expect to use this field in multiple views, and allow users to simply select this field as if it was another table field. From a user’s point of view, a SQL Expression Field behaves the same as a normal table field.
Adding a SQL Expression Field to a Table
To create a new SQL Expression Field, go to the table and list the fields. If a SQL Expression Field is allowed for this table, the "New SQL Expression Field" button is visible. Creating a SQL Expression Field is allowed for relational databases such as Oracle, PostgreSQL, MySQL, DB2, SQL Server, and Access.
Clicking the button brings up a confirmation dialog and then the "New SQL Expression Field" dialog:
- Name identifies the field (it can be the same as label).
- Label is the name you see when selecting the field in a view.
- Type is the field datatype such as date/time.
- SQL Expression is the SQL-language expression that defined the calculation.
When you click OK, Explore Analytics will validate the SQL expression by performing a simple query that includes the expression. If validation fails, you'll see a data-source specific error message. You'll need to correct the SQL Expression and try again.
SQL Expression
The SQL expression can refer to any field in the table and can use SQL functions and operators. Aggregate functions such as SUM and AVG are not allowed.
SQL Expression Examples
Suppose your table has a "price" field and a "quantity" field. You can calculate the "extended" field as follows:
"price" * "quantity"
Suppose your table has an 8-digit character field named "date" with a 4-digit year followed by a 2-digit month followed by a 2-digit day of the month (for example, 20160704 for July 4, 2016). For Oracle, the expression to convert the "date" field to a proper date/time is:
TO_DATE("date",'YYYYMMDD')
Now suppose that your table also has a 4-digit character field named "time" with a 2-digit hours (24-hour clock) followed by 2-digit minutes (for example 1805 for 6:05pm). For Oracle, the expression to combine the "date" and "time" fields into a date/time is:
TO_DATE("date"||"time",'YYYYMMDDHH24MI')
Referring to table field when getting data from multiple tables
A view may refer to more than one table by using reference fields. In such a case, a field name such as “date” may become ambiguous if it exists in more than one table. To direct Explore Analytics to properly qualify the field with the correct table, replace “date” in the SQL expression with ${date}. Explore Analytics will substitute the correct syntax for ${date}, for example "t0"."date" to denote the date field of the first table.
The expression from the previous example should therefore be:
TO_DATE(${date}||${time},'YYYYMMDDHH24MI')
For the extended price example, the expression should be:
${price} * ${quantity}
A MySQL Example
The previous example was for an Oracle table. The same for a MySQL table would be:
STR_TO_DATE(CONCAT(${date}, ${time}), '%d%m%Y%H%i')
Deleting a SQL Expression Field
You can delete a SQL Expression Field that you have previously created by selecting "Delete Field" from the field list context menu:
