Skip to content

Writing SQL Queries

Rational BI uses the SQLite database engine for data storage, data transfers and database queries.

By default, the user interface automatically constructs queries and executes them against the database based on report component configuration, but it is also possible to make direct queries against the SQLite database.

Enabling SQL mode

Enable SQL query mode in the view settings of the design studio to edit the SQL query directly.

Tip

The data studio will initialize the editor with the SQL query representing the current state of the report component configuration when first invoked. To reset the editor to the default query, erase any edited query and toggle SQL mode on and off momentarily.

SQL dialect

SQLite understands most of the standard SQL language. Some features are omitted and not supported.

Refer to the SQLite SQL reference for information about specific language support.

Rational BI uses a SQLite version > 3.25 which means that SQL Window Functions are supported. Refer to the SQLite Introduction to Window Functions for more information.

Example Query

SELECT tennis_1.winner_name AS winner_name_1,
COUNT(*) AS _star_1
FROM tennis.tennis tennis_1 
WHERE (tennis_1.tourney_date >= 1514764800 AND tennis_1.tourney_date <= 1546300799)
GROUP BY winner_name_1
ORDER BY _star_1 desc
LIMIT 1000

Handling of dates

Dates are stored as integers containing UNIX Time values in the database.

Note

UNIX time is in Coordinated Universal Time (UTC). The user interface uses the browser localization functions to convert the time to local time based on the locale settings of the workspace.

Example time conversion from Unix time

SELECT 
    cast(strftime('%s', datetime(tennis_1.tourney_date, 'unixepoch', 'start of day')) as integer) + 
    cast(strftime('%H', datetime(tennis_1.tourney_date, 'unixepoch')) as integer) * 3600 AS game_date

Handling of booleans

Boolean values are stored as 1 and 0 for true and false respectively.

Handling of currencies

Currency values are stored as the value * 100 in order to not lose precision when applying aggregate functions.