create_sql_case_when: Create a reusable SQL case_when function

In RLesur/casewhen: Create Reusable 'dplyr' 'case_when()' Functions

Description

This function is a helper devoted to developers who want to add a custom case_when function to a SQL translator. In this case, you should use the fn argument instead of the con argument.

Usage

create_sql_case_when(..., vars = "x", con = NULL,
  fn = dplyr::sql_translate_env(con = con)$scalar$case_when)

Arguments

...	A sequence of two-sided formulas. The left hand side (LHS) determines which values match this case. The right hand side (RHS) provides the replacement value. The LHS must evaluate to a logical vector. Each logical vector can either have length 1 or a common length. All RHSs must evaluate to the same type of vector. These dots are evaluated with explicit splicing.
vars	A character vector that determined the names of the arguments of the returned function.
con	A database connection.
fn	A function to be used by the returned function.

Examples

con <- structure(
  list(),
  class = c("TestCon", "Oracle", "DBITestConnection", "DBIConnection")
)

fn <- dplyr::sql_translate_env(con)$scalar$case_when

cw_fb <- create_sql_case_when(
  number %% 35 == 0 ~ "fizz buzz",
  number %% 5 == 0 ~ "fizz",
  number %% 7 == 0 ~ "buzz",
  TRUE ~ as.character(number),
  vars = "number",
  fn = fn
)

testcon_var <- dbplyr::sql_variant(
  dbplyr::sql_translator(
    cw_fb = cw_fb,
    .parent = dplyr::sql_translate_env(con)$scalar
  ),
  dplyr::sql_translate_env(con)$aggregate,
  dplyr::sql_translate_env(con)$window
)

sql_translate_env.TestCon <- function(x) testcon_var

dbplyr::translate_sql(cw_fb(x), con = con)

RLesur/casewhen documentation built on May 5, 2019, 12:29 a.m.