Translate an expression to sql.

translate_sql(..., con = NULL, 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

# Regular maths is translated in a very straightforward way translate_sql(x + 1)
#> <SQL> "x" + 1.0
translate_sql(sin(x) + tan(y))
#> <SQL> SIN("x") + TAN("y")
# Note that all variable names are escaped translate_sql(like == "x")
#> <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))
#> <SQL> "x" < 5.0 AND NOT(("y" >= 5.0))
# xor() doesn't have a direct SQL equivalent translate_sql(xor(x, y))
#> <SQL> "x" OR "y" AND NOT ("x" AND "y")
# If is translated into case when translate_sql(if (x > 5) "big" else "small")
#> <SQL> CASE WHEN ("x" > 5.0) THEN ('big') WHEN NOT("x" > 5.0) THEN ('small') END
# Infix functions are passed onto SQL with % removed translate_sql(first %like% "Had%")
#> <SQL> "first" LIKE 'Had%'
translate_sql(first %is% NULL)
#> <SQL> "first" IS
translate_sql(first %in% c("John", "Roger", "Robert"))
#> <SQL> "first" IN ('John', 'Roger', 'Robert')
# And be careful if you really want integers translate_sql(x == 1)
#> <SQL> "x" = 1.0
translate_sql(x == 1L)
#> <SQL> "x" = 1
# If you have an already quoted object, use translate_sql_: x <- quote(y + 1 / sin(t)) translate_sql_(list(x))
#> <SQL> "y" + 1.0 / SIN("t")
# Windowed translation -------------------------------------------- # Known window functions automatically get OVER() translate_sql(mpg > mean(mpg))
#> Warning: Missing values are always removed in SQL. #> Use `avg(x, na.rm = TRUE)` to silence this warning
#> <SQL> "mpg" > avg("mpg") OVER ()
# Suppress this with window = FALSE translate_sql(mpg > mean(mpg), window = FALSE)
#> Warning: Missing values are always removed in SQL. #> Use `AVG(x, na.rm = TRUE)` to silence this warning
#> <SQL> "mpg" > AVG("mpg")
# vars_group controls partition: translate_sql(mpg > mean(mpg), vars_group = "cyl")
#> Warning: Missing values are always removed in SQL. #> Use `avg(x, na.rm = TRUE)` to silence this warning
#> <SQL> "mpg" > avg("mpg") OVER (PARTITION BY "cyl")
# and vars_order controls ordering for those functions that need it translate_sql(cumsum(mpg))
#> Warning: Windowed expression 'sum("mpg")' does not have explicit order. #> Please use arrange() or window_order() to make determinstic.
#> <SQL> sum("mpg") OVER (ROWS UNBOUNDED PRECEDING)
translate_sql(cumsum(mpg), vars_order = "mpg")
#> <SQL> sum("mpg") OVER (ORDER BY "mpg" ROWS UNBOUNDED PRECEDING)