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() )

..., dots | Expressions to translate. |
---|---|

con | An optional database connection to control the details of
the translation. The default, |

vars | Deprecated. Now call |

vars_group, vars_order, vars_frame | Parameters used in the |

window | Use |

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

The base translator, `base_sql`

, provides custom mappings for for
commonly used base functions including logical (`!`

, `&`

, `|`

),
arithmetic (`^`

), and comparison (`!=`

) operators, as well as common
summary (`mean()`

, `var()`

) and manipulation functions.

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.

The SQLite variant currently only adds one additional function: a mapping
from `sd()`

to the SQL aggregation function `STDEV`

.

# Regular maths is translated in a very straightforward way translate_sql(x + 1)#> <SQL> `x` + 1.0#> <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))#> <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% NA)#> <SQL> `first` is NULL#> <SQL> `first` IN ('John', 'Roger', 'Robert')# And be careful if you really want integers translate_sql(x == 1)#> <SQL> `x` = 1.0translate_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), con = simulate_dbi())#> <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 #> This warning is displayed only once per session.#> <SQL> `mpg` > AVG(`mpg`) OVER ()#> Warning: Missing values are always removed in SQL. #> Use `mean(x, na.rm = TRUE)` to silence this warning #> This warning is displayed only once per session.#> <SQL> `mpg` > AVG(`mpg`)#> <SQL> `mpg` > AVG(`mpg`) OVER (PARTITION BY `cyl`)#> 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)#> <SQL> SUM(`mpg`) OVER (ORDER BY `mpg` ROWS UNBOUNDED PRECEDING)