1

I've seen a similar question here and example 5 here but i can't get my example to work. I'm not very experienced in R so maybe it's something silly. I just want to have a function that the argument is a column name used by sqldf

My best guess:

function1 <- function(x) {
    ranking_test <<- sqldf ('select $x from lat_lon_combo t1')
}

function1(t1.lat_lon)

results in

"Error in mget(words, envir, "any", NA, inherits = TRUE) : object 't1.lat_lon' not found"

Although it t1.lat_lon should exist. Any ideas?

Community
  • 1
  • 1
khhc
  • 93
  • 2
  • 11
  • 1
    Replace `sqldf` with `fn$sqldf` and then `function1("abc")` will work if `abc` is a valid column name that does not contain special characters.. See example 5 on the sqldf home page: https://github.com/ggrothendieck/sqldf – G. Grothendieck Jul 18 '16 at 00:22
  • Very nice! Thanks so t1.$x get's the job done in this circumstance. Thanks for the package, it's proved super useful for me. – khhc Jul 19 '16 at 03:07

1 Answers1

0

Common mistake. The column name t1.lat_lon may exist, but in your call to function1, you are referencing a variable named t1.lat_lon, which does not exist.

You have a couple of options, and for simplicity I recommend the first (strings):

  1. Pass it as a string, and use paste or sprintf to form the query string. Such as:

    library(sqldf)
func1 <- function(x) {
    sqldf(sprintf("select %s from mtcars where cyl > 7", x))
}
func1("disp")

  2. If you really must reference it without the quotes, you can do this trickery, but in this case it is unnecessary complexity (for the sake of two quotation mark symbols):

    func2 <- function(x) {
    sqldf(sprintf("select %s from mtcars where cyl > 7",
                  deparse(substitute(x))))
}
func2(disp)

    This type of dereferencing is used (and useful) when doing something like plot(disp ~ mpg, data = mtcars) (though that does things even slightly differently with a formula). In those situations it adds usefulness at the expense of some code complexity and indirection. If you don't need this (and, based on your example, it does not look like you do), then I would avoid this for the sake of simplicity.

BTW: in both cases, I return the value returned from sqldf, I chose (and I strongly urge) against side-effects as you did in your function. Side effects can be desired but, if avoidable, are opined to provide cleaner and more predictable functions.

Community
  • 1
  • 1
r2evans
  • 141,215
  • 6
  • 77
  • 149
  • Thank you very much It works well! It's all one big string of course! About the side affects, are you saying it's unwise to use a function to write to the "ranking_test" variable? – khhc Jul 17 '16 at 15:17
  • For the downvote, can you help me understand what can be improved? It's slightly hackish, sure, but a comment would help improve me and the answer for future reference. – r2evans Jul 18 '16 at 16:48
  • 1
    Normally one uses `fn$sqldf` to perform string interpolation in the SQL string as per comment to question. – G. Grothendieck Jul 18 '16 at 22:03
  • I see, the comment wasn't there (or visible, at least) when I answered. I'm not as familiar with the `fn$` method, very interesting to see. Thanks for responding! – r2evans Jul 18 '16 at 23:01