# translate_sql

##### Translate an expression to sql.

Translate an expression to sql.

##### Usage

```
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

```
# 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% NULL)
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))
# 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")
# }
```

*Documentation reproduced from package dbplyr, version 1.2.1, License: MIT + file LICENSE*