development:sql
Differences
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. |
development/sql.1422973891.txt.gz · Last modified: 2015/02/03 14:31 by vojtechhuser
Page Tools
