Observational Health Data Sciences and Informatics

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.

CDM data can live on different databases.

SqlRender package provides a way to translate SQL statements across dialects. I relies on a CSV table that translates from source dialect to target dialect.Because of temporal issues, the source has to be MSSQLServer dialect.


Another great feature of the package is ability to write SQL code that has variables. There is no "ANSI PL/SQL", hence this functionality. It has excellent possibilities.

Eg, "@my_parameter" gets replaced with the value of it (for example the string 'diabetesAnalysis'.

====== Guidelines for writing parametized SQL code ======

You never know in what database you may be in, so before accessing any CDM data (e.g., death table, use the following trick

select * from @cdm_database_schema.death 

===== Martijn guide =====


Rename parameters of the R function using parameterized SQL from cdmSchema to cdmDatabaseSchema
Change the help for the function to indicate that for SQL Server the cdmDatabaseSchema should include '.dbo' at the end (or whatever the schema is).
Within the R function, add something like this: cdmDatabase <- strsplit(cdmDatabaseSchema ,"\\.")[[1]][1]
In the parameterized SQL, rename the @cdm_schema parameter to @cdm_database_schema, and remove any `.dbo' you can find.
In the parameterized SQL, add a parameter @cdm_database, and use that when calling the USE function instead of @cdm_database_schema.
Don't forget to pass the cdmDatabase parameter in R to the @cdm_database parameter in SQL when calling SqlRender.

Observational Health Data Sciences and Informatics

User Tools

Site Tools

Page Tools