Take this query:
SELECT EXTRACT(month FROM order_date) "Month"
FROM orders
(simplified example from official oracle doc)
How would you go at integrating such EXTRACT operations above in a dbplyr chain ?
I'm open to any other workaround (even ugly/costly) to extract the month on server side.
Meanwhile I came up with something.
The expected output of given example will be obtained by executing this:
con <- ROracle::dbConnect(drv, username, password, dbname) # your connection parameters
dplyr::tbl(con,"orders") %>%
extract_o("Month","order_date",append = FALSE,force_upper_case = FALSE)
Here is the function's code, I included some parameters to force upper case columns (default) and to append new column to existing ones (default). Name of new column can be defined or by default will be named as the type of value you want to extract.
#' use Oracle EXTRACT function
#'
#' Will add a column to the table, containing extracted value,
#' optionally returns only this column
#' @param data tbl_lazy object
#' @param what type of data to extract
#' @param from column to extract from
#' @param new_col name of new column
#' @param append keep existing columns,
#' FALSE ditches them and keep only extracted column
#' @param force_upper_case make new column name uppercase
extract_o <-function(data, what, from, new_col = what,
append = TRUE,force_upper_case = TRUE) {
allowed <- c("day","month","year","hour","minute","second",
"timezone_hour","timezone_minute",
"timezone_region","timezone_abbr")
assertthat::assert_that(
tolower(what) %in% allowed,
msg=paste("Choose 'what' among",
paste0("'",allowed,"'",collapse=", ")))
if(force_upper_case) new_col <- toupper(new_col)
tbl_query <- as.character(dbplyr::sql_render(data)) # previous query
append_sql <- if(append)
paste0(paste(colnames(data),collapse=", "),", ") else ""
query <- paste0("SELECT ", append_sql, # initial cols or none
"EXTRACT(",what," FROM ",from,") \"",new_col, # new col
"\" FROM (",tbl_query,")") # previous query
dplyr::tbl(data$src$con,sql(query))
}