Using pl/sql trunc function with dbplyr in R

Question

I am trying to use dbplyr and the trunc function from pl/sql, to mutate the date column to the start of the month.

df %>% mutate(start_month = sql(trunc(date_column, 'month')) 

however this throws an error invalid identifier when executing the query. I think it is because when it is parsed to pl/sql as a string the query reads select .... trunc("date_column",'month') as start_month so it doesn't recognise as a column name due to the quotes inside the sql function.... Any ideas on how to do this another way or get around this error would be great.

Answer 1

You can probably achieve this by removing the quote marks from month and possible the sql function from inside your mutate.

dbplyr works by translating dplyr commands into the equivalent sql. Where there is no translation it defaults to leaving the command as is. You can make use of this feature to pass in sql commands.

I recommend trying

df %>% mutate(start_month = TRUNC(date_column, MONTH))

As dbplyr translations are not defined for TRUNC or MONTH these should appear in your plsql query in the same way as they appear in your R code:

SELECT ... TRUNC(date_column, MONTH) AS start_month

I recommend writing the commands you do not want translated in capitals because R is case sensitive but sql is not. So even if month is an R function, MONTH is probably not an R function and hence there is no chance dbplyr will try and translate it.