SQL Statements can be used inside TERR in several ways. One of these is the open source R package sqldf, which can run SQL statements on R data frames. The user simply specifies an SQL statement in R using data frame names in place of table names. As a result, several things happen:
- A database with appropriate table layouts/schema is automatically created
- The data frames are automatically loaded into the database
- The specified SQL statement is performed
- The result is read back into R
- The database is deleted, all automatically behind the scenes, making the database’s existence transparent to the user who only specifies the SQL statement.
Check out this resource to learn more about sqldf.
Here’s an example illustrating SQL in Spotfire data functions in three steps:
1. Input data: A simple data table, which contains a brief description about quantity of fruits and vegetables, is loaded into Spotfire.
3. The following script can be used to execute SQL statement and then return output as an out table.
You can view final results in Spotfire as only Fruits returned after SQL query execution.