How to use "sqldf" in a function

Question

I am trying with the below code but getting the error: I tried with both "sprintf", "fn$sqldf" to select the output. my function will run two times one with the column "a" and another with column "e". Please suggest. I got this suggestion from some member and edit the code.enter code here

db2 = data.frame(a = c(1,2,3), 
                 b = c("b","a","c"), 
                 e=  c(4,6,3))

library(sqldf)
       extrct = function(id)
   {
       filedata1 <-  sprintf("select max(%s) as maxid from db2",id)
       filedata3 <- fn$sqldf("select max(id) as maxid from db2")

    }
    extrct(a)
    extrct(e)

ERROR:

Error in sprintf("select max(%s) as maxid from db2", id) : 
  object 'a' not found
Error in sprintf("select max(%s) as maxid from db2", id) : 
  object 'e' not found

`filedata <- sqldf(paste0("select name, max(",id,") from income"))` — samkart, Sep 30 '18 at 16:10
That is because you need to pass in the value of the variable id to your sql statement. You can do it using `sprintf`. Try using the query `sprintf("select name, max(%s) from income",id) ` — R.S., Sep 30 '18 at 16:10
Please provide a complete example including all `library` statements, the values of all inputs, the error message, the expected output and a description in words of what you are trying to do. — G. Grothendieck, Sep 30 '18 at 16:40
Here's a very similar question that asked for using PostgresSQL: https://stackoverflow.com/questions/21316946/call-sql-function-within-r-function and one that asks for multiple queries: https://stackoverflow.com/questions/19636760/wrapping-a-function-around-multiple-sql-queries-in-r/19637101#19637101 and this one looks pretty much like an answer to an exact duplicate: https://stackoverflow.com/questions/32188139/function-create-with-sqldf-in-r/32189346?s=5|31.9807#32189346 — IRTFM, Sep 30 '18 at 19:28

G. Grothendieck · Answer 1 · 2018-09-30T18:53:05.673

2

1) In the absence of a complete example in the question maybe you want to find the row in income corresponding to a specific id -- below we find the row in income having id equal to 3.

library(sqldf)

income <- data.frame(name = letters, id = 1:26) # test data

newvalue <- function(income, id) {
  fn$sqldf("select * from income where id = $id")
}

newvalue(income, 3)
##   name id
## 1    c  3

2) or using base R:

newvalue2 <- function(income, id) income[income$id == id, ]
newvalue2(income, 3)
##   name id
## 3    c  3

edited Sep 30 '18 at 18:53

answered Sep 30 '18 at 18:04

G. Grothendieck

254,981
17
203
341

@GGrothendieckThis is interesting. I did not know you could use a variable substitution/evaluation with `fn$sqldf` . Apparently sqldf uses `gsubfn` package to do this. https://cran.r-project.org/web/packages/sqldf/README.html . Will explore it further. Thanks for sharing this code – R.S. Oct 01 '18 at 05:30

How to use "sqldf" in a function

1 Answers1