Execute a SQL query on tables — opal.sql • opalr

The SQL query can apply to raw tables and/or views and require the permission to view the values of these tables. When all tables belong to a project, it is possible to simplify the SQL query by providing the project name parameter. Otherwise the fully qualified table names (`<project>.<table>`) must be specified in the FROM statements.

Usage

opal.sql(opal, query, project = NULL, id.name = "_id")

Arguments

opal: Opal connection object.
query: The SQL query statement.
project: Project name where the table(s) are located. If not provided, the SQL query must refer to the full table name(s) (use backquotes to escape, see examples).
id.name: The name of the column representing the entity identifiers. Default is '_id'.

Value

The lists of columns and rows, as a data.frame.

Examples

if (FALSE) { # \dontrun{
o <- opal.login('administrator','password', url='https://opal-demo.obiba.org')
# with project context
opal.sql(o,  
  'select avg(LAB_HDL) as HDL_AVG, GENDER 
     from CNSIM1 
     where LAB_HDL is not null 
     group by GENDER',
  'CNSIM')
# without project context
opal.sql(o,
  'select avg(LAB_HDL) as HDL_AVG, GENDER 
     from `CNSIM.CNSIM1` 
     where LAB_HDL is not null 
     group by GENDER')
opal.logout(o)
} # }