dbplyr (version 1.4.0)

translate_sql: Translate an expression to sql.

Description

Translate an expression to sql.

Usage

translate_sql(..., con = simulate_dbi(), vars = character(),
  vars_group = NULL, vars_order = NULL, vars_frame = NULL,
  window = TRUE)

translate_sql_(dots, con = NULL, vars_group = NULL, vars_order = NULL, vars_frame = NULL, window = TRUE, context = list())

Arguments

..., dots

Expressions to translate. translate_sql() automatically quotes them for you. translate_sql_() expects a list of already quoted objects.

con

An optional database connection to control the details of the translation. The default, NULL, generates ANSI SQL.

vars

Deprecated. Now call partial_eval() directly.

vars_group, vars_order, vars_frame

Parameters used in the OVER expression of windowed functions.

window

Use FALSE to suppress generation of the OVER statement used for window functions. This is necessary when generating SQL for a grouped summary.

context

Use to carry information for special translation cases. For example, MS SQL needs a different conversion for is.na() in WHERE vs. SELECT clauses. Expects a list.

Base translation

The base translator, base_sql, provides custom mappings for ! (to NOT), && and & to AND, || and | to OR, ^ to POWER, %>% to %, ceiling to CEIL, mean to AVG, var to VARIANCE, tolower to LOWER, toupper to UPPER and nchar to LENGTH.

c() and : keep their usual R behaviour so you can easily create vectors that are passed to sql.

All other functions will be preserved as is. R's infix functions (e.g. %like%) will be converted to their SQL equivalents (e.g. LIKE). You can use this to access SQL string concatenation: || is mapped to OR, but %||% is mapped to ||. To suppress this behaviour, and force errors immediately when dplyr doesn't know how to translate a function it encounters, using set the dplyr.strict_sql option to TRUE.

You can also use sql() to insert a raw sql string.

SQLite translation

The SQLite variant currently only adds one additional function: a mapping from sd() to the SQL aggregation function STDEV.

Examples

Run this code
# NOT RUN {
# Regular maths is translated in a very straightforward way
translate_sql(x + 1)
translate_sql(sin(x) + tan(y))

# Note that all variable names are escaped
translate_sql(like == "x")
# In ANSI SQL: "" quotes variable _names_, '' quotes strings

# Logical operators are converted to their sql equivalents
translate_sql(x < 5 & !(y >= 5))
# xor() doesn't have a direct SQL equivalent
translate_sql(xor(x, y))

# If is translated into case when
translate_sql(if (x > 5) "big" else "small")

# Infix functions are passed onto SQL with % removed
translate_sql(first %like% "Had%")
translate_sql(first %is% NA)
translate_sql(first %in% c("John", "Roger", "Robert"))

# And be careful if you really want integers
translate_sql(x == 1)
translate_sql(x == 1L)

# If you have an already quoted object, use translate_sql_:
x <- quote(y + 1 / sin(t))
translate_sql_(list(x), con = simulate_dbi())

# Windowed translation --------------------------------------------
# Known window functions automatically get OVER()
translate_sql(mpg > mean(mpg))

# Suppress this with window = FALSE
translate_sql(mpg > mean(mpg), window = FALSE)

# vars_group controls partition:
translate_sql(mpg > mean(mpg), vars_group = "cyl")

# and vars_order controls ordering for those functions that need it
translate_sql(cumsum(mpg))
translate_sql(cumsum(mpg), vars_order = "mpg")
# }

Run the code above in your browser using DataCamp Workspace