CODEWITHSUNDEEP
sqlrsqldf
user3698033
user3698033

Reputation: 39

pass a column name correctly as a function argument for SQL query in R

I have a datafile sales_history. I want to query it in the following way. 

my_df<-sqldf("SELECT * 
                    FROM sales_history
                    WHERE Business_Unit=='RETAIL'"")

Now I want to write a function with argument datafile and column name to do the above job. So something like: 

pick_column<-function(df, column_name){
  my_df<-sqldf("SELECT * 
                      FROM df
                      WHERE Business_Unit==column_name"
  return(my_df)
}

Ideally, after running the above function definition, I should then be able to run pick_column(sales_history,'RETAIL'). But when I do this, the second argument 'RETAIL' is not passed to the function correctly. What's the correct way to do this then?

I know that for this example, there are other ways to do this other than using "sqldf" for SQL query. But the point of my question here is how to pass the column_name correctly as a function argument.

Upvotes: 0

Views: 391

Answers (1)

MrFlick
MrFlick

Reputation: 206566

the sqldf package uses gsubfn to allow you to add names of R variables into your SQL commands by prefixing them with the "$" character. So you can write

sales_history <- data.frame(
    price=c(12,10),
    Business_Unit=c("RETAIL","BUSINESS"),
    stringsAsFactors=F
)

pick_column <- function(df, columnname) {
    fn$sqldf("SELECT * FROM $df WHERE Business_Unit='$columnname'")
}

pick_column("sales_history","RETAIL")

Upvotes: 4

Related Questions