Sidebar
development:sql
This is an old revision of the document!
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.
Guidelines for writing parametized SQL code
You never know in what database you may be in, so before accessing any CDM data (e.g., person table, use the following trick
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.
development/sql.1422973891.txt.gz · Last modified: 2015/02/03 14:31 by vojtechhuser
