Load, render, and translate a SQL file in a package

loadRenderTranslateSql Loads a SQL file contained in a package, renders it and translates it to the specified dialect

loadRenderTranslateSql(
  sqlFilename,
  packageName,
  dbms = "sql server",
  ...,
  tempEmulationSchema = getOption("sqlRenderTempEmulationSchema"),
  oracleTempSchema = NULL,
  warnOnMissingParameters = TRUE
)

Arguments

sqlFilename

The source SQL file

packageName

The name of the package that contains the SQL file

dbms

The target dialect. Currently 'sql server', 'oracle', 'postgres', and 'redshift' are supported

...

Parameter values used for render

tempEmulationSchema

Some database platforms like Oracle and Impala do not truly support temp tables. To emulate temp tables, provide a schema with write privileges where temp tables can be created.

oracleTempSchema

DEPRECATED: use tempEmulationSchema instead.

warnOnMissingParameters

Should a warning be raised when parameters provided to this function do not appear in the parameterized SQL that is being rendered? By default, this is TRUE.

Value

Returns a string containing the rendered SQL.

Details

This function looks for a SQL file with the specified name in the inst/sql/<dbms> folder of the specified package. If it doesn't find it in that folder, it will try and load the file from the inst/sql or inst/sql/sql_server folder and use the translate function to translate it to the requested dialect. It will subsequently call the render function with any of the additional specified parameters.

Examples

if (FALSE) {
renderedSql <- loadRenderTranslateSql("CohortMethod.sql",
  packageName = "CohortMethod",
  dbms = connectionDetails$dbms,
  CDM_schema = "cdmSchema"
)
}