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.
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.
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.
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.
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.