How can I pass a column name as a parameter to SQL in R sqldf?
q <- "Q10"
A = fn$sqldf('SELECT * FROM Customer WHERE $q < 100')
q <- "Q10"
A = fn$sqldf('SELECT * FROM Customer WHERE '$q' < 100')
sprintf() will work charmingly in this case. Personally, I find code using
sprintf() to be more easy to read (and even write) than
q <- "Q10" sql <- sprintf("SELECT * FROM Customer WHERE %s < 100", q) sql  "SELECT * FROM Customer WHERE Q10 < 100"
In fact, you can go much further with compiling complex SQL queries. Just to illustrate:
q <- "Q10" value <- "150" sql <- sprintf("SELECT * FROM Customer WHERE %s < %s", q, value) sql  "SELECT * FROM Customer WHERE Q10 < 150"
You can use
paste command to concatenate variable and string.
> library(sqldf) > my_names <- names(sqldf("select * from iris limit 10")) > sqldf(paste("select",my_names, "from iris limit 2", sep=" ")) Sepal_Length 1 5.1 2 4.9
fn$ to perform the string interpolation, the last line could be written:
> fn$sqldf("select `my_names` from iris limit 2")
The names might be different when you try to use sqldf to query.
For example, the default names for dataset
> names(iris)  "Sepal.Length" "Sepal.Width" "Petal.Length" "Petal.Width" "Species"
However, the names have to be formatted in a different way when you try to use the column names to make queries:
> sqldf("select * from iris limit 1") Sepal_Length Sepal_Width Petal_Length Petal_Width Species 1 5.1 3.5 1.4 0.2 setosa
This is the official explaination:
Here is an example of sorting and limiting output from an SQL select statement on the iris data frame that comes with R. Note that although the iris dataset uses the name Sepal.Length the RSQLite layer converts that to Sepal_Length.
So that is the reason in my solution I first created the
my_names variable from
sqldf select statement instead of
For me the sprintf is the perfect solution, you need to change the %s by %d for integers i think.
q= 'Q10' ; value= 150 sql <- sprintf("SELECT * FROM Customer WHERE %s < %d", q, value) sql  "SELECT * FROM Customer WHERE Q10 < 150"