This shows you the differences between two versions of the page.
development:sql [2015/02/03 14:31] vojtechhuser created |
development:sql [2015/02/03 14:35] (current) vojtechhuser |
||
---|---|---|---|
Line 1: | Line 1: | ||
CDM data can live on different databases. | 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. | + | 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. |
- | Because of temporal issues, the source has to be MSSQLServer dialect. | ||
- | ====== Guidelines for writing parametized SQL code ====== | + | 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. |
- | You never know in what database you may be in, so before accessing any CDM data (e.g., person table, use the following trick | + | 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 ===== | ===== Martijn guide ===== | ||
- | Rename parameters of the R function using parameterized SQL from cdmSchema to cdmDatabaseSchema | + | 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). | + | 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] | + | 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, 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. | + | 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. | + | Don't forget to pass the cdmDatabase parameter in R to the @cdm_database parameter in SQL when calling SqlRender. |