EVOLUTION-MANAGER
Edit File: translate_sql.html
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><title>R: Translate an expression to sql.</title> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <link rel="stylesheet" type="text/css" href="R.css" /> </head><body> <table width="100%" summary="page for translate_sql {dbplyr}"><tr><td>translate_sql {dbplyr}</td><td style="text-align: right;">R Documentation</td></tr></table> <h2>Translate an expression to sql.</h2> <h3>Description</h3> <p>Translate an expression to sql. </p> <h3>Usage</h3> <pre> 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() ) </pre> <h3>Arguments</h3> <table summary="R argblock"> <tr valign="top"><td><code>..., dots</code></td> <td> <p>Expressions to translate. <code>translate_sql()</code> automatically quotes them for you. <code>translate_sql_()</code> expects a list of already quoted objects.</p> </td></tr> <tr valign="top"><td><code>con</code></td> <td> <p>An optional database connection to control the details of the translation. The default, <code>NULL</code>, generates ANSI SQL.</p> </td></tr> <tr valign="top"><td><code>vars</code></td> <td> <p>Deprecated. Now call <code><a href="partial_eval.html">partial_eval()</a></code> directly.</p> </td></tr> <tr valign="top"><td><code>vars_group, vars_order, vars_frame</code></td> <td> <p>Parameters used in the <code>OVER</code> expression of windowed functions.</p> </td></tr> <tr valign="top"><td><code>window</code></td> <td> <p>Use <code>FALSE</code> to suppress generation of the <code>OVER</code> statement used for window functions. This is necessary when generating SQL for a grouped summary.</p> </td></tr> <tr valign="top"><td><code>context</code></td> <td> <p>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.</p> </td></tr> </table> <h3>Base translation</h3> <p>The base translator, <code>base_sql</code>, provides custom mappings for <code>!</code> (to NOT), <code>&&</code> and <code>&</code> to <code>AND</code>, <code>||</code> and <code>|</code> to <code>OR</code>, <code>^</code> to <code>POWER</code>, <code style="white-space: pre;">%>%</code> to <code style="white-space: pre;">%</code>, <code>ceiling</code> to <code>CEIL</code>, <code>mean</code> to <code>AVG</code>, <code>var</code> to <code>VARIANCE</code>, <code>tolower</code> to <code>LOWER</code>, <code>toupper</code> to <code>UPPER</code> and <code>nchar</code> to <code>LENGTH</code>. </p> <p><code>c()</code> and <code>:</code> keep their usual R behaviour so you can easily create vectors that are passed to sql. </p> <p>All other functions will be preserved as is. R's infix functions (e.g. <code style="white-space: pre;">%like%</code>) will be converted to their SQL equivalents (e.g. <code>LIKE</code>). You can use this to access SQL string concatenation: <code>||</code> is mapped to <code>OR</code>, but <code style="white-space: pre;">%||%</code> is mapped to <code>||</code>. To suppress this behaviour, and force errors immediately when dplyr doesn't know how to translate a function it encounters, using set the <code>dplyr.strict_sql</code> option to <code>TRUE</code>. </p> <p>You can also use <code><a href="sql.html">sql()</a></code> to insert a raw sql string. </p> <h3>SQLite translation</h3> <p>The SQLite variant currently only adds one additional function: a mapping from <code>sd()</code> to the SQL aggregation function <code>STDEV</code>. </p> <h3>Examples</h3> <pre> # 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") </pre> <hr /><div style="text-align: center;">[Package <em>dbplyr</em> version 1.4.4 <a href="00Index.html">Index</a>]</div> </body></html>