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