Differences

This shows you the differences between two versions of the page.

--- development:sql [2015/02/03 14:31] – created vojtechhuser
+++ development:sql [2015/02/03 14:35] (current) – vojtechhuser
@@ Line 1: / Line 1: @@
 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 =====
-    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.