Ifelse & grepl when using dplyr for SQL in-db operations

Question

In dplyr running on R data frames, it's easy to run

df <- df %>% mutate(income_topcoded = ifelse(income > topcode, income, topcode) 

Now I am working with a large SQL database, using dplyr to send commands to the SQL server. When I run the same command, I return

 Error in postgresqlExecStatement(conn, statement, ...) : RS-DBI driver: (could not Retrieve the result : ERROR: function ifelse (boolean, numeric, numeric) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. 

How do you suggest implementing ifelse() statements? I would be fine with something in PivotalR (which seems to support ifelse() , but I don't know how to integrate it with dplyr and could not find any examples in SO), some piece of SQL syntax that I can use here , or some dplyr feature that I did not know about.

(I have the same problem that I would like to use grepl() as an in-db operation, but I don't know how to do this.)

Answer 1

Based on @hadley's answer to this thread , you can use the if() SQL-style mutate() statement in dftr-in-db:

 df <- df %>% mutate( income_topcoded = if (income > topcode) income else topcode) 

Since using grepl() goes ... well, you cannot. But you can use the SQL like statement:

 df <- df %>% filter( topcode %like% "ABC%" ) 

Answer 2

I had a similar problem. The best I could do was use the in-db operation, as you suggest:

 topcode <- 10000 queryString <- sprintf("UPDATE db.table SET income_topcoded = %s WHERE income_topcoded > %s",topcode,topcode) dbGetQuery(con, queryString) 

In my case, I used MySQL with dplyr , but it could not translate my ifelse() into valid SQL.