How to include a user defined variable in sqldf query?

Question

I have a list of values that I want to parse through in the future. For the time being to ensure that I have the format correct for this query. I have this query:

var2 = "Application Maintenance"

r = fn$sqldf("Select Iteration, (SUM(Hours) / SUM(Effort)) as Efficiency
     From df WHERE Iteration = $var2") 

query = sprintf('Select Iteration, (SUM(Hours) / SUM(Effort)) 
as Efficiency from df WHERE Iteration = %s', var2)

q = sqldf(query)

I am getting the error in both attempts: Error in result_create(conn@ptr, statement) : near "Maintenance": syntax error

I have only used sqldf once in the past so I am still new to the syntax. The query worked just fine when I changed $var2 to "Application Maintenance" but is not working when I use a user defined variable.

I've tried solutions from other Stackoverflow posts, but I kept getting the same error above.

Any help would be much appreciated!

Edit: Code for list iteration (path_list is a list of strings): getting NULL 'Error in tcltk::as.tclObj(X) : cannot handle object of mode 'list''

for (var1 in path_list){
  query = fn$sqldf("Select Iteration, (SUM(Hours) / SUM(Effort)) as Efficiency 
  From df WHERE Iteration = '$var1'")
  print(query)

}

Edit 2: The path_list is contained in this format (when clicking on the list in RStudio:

       Iteration
'Application Maintenance'
'Task'
'QA'

Edit 3: Included Example of first 15 rows. There are thousands in this data set. Changed 'Iteration' column names for confidentiality reasons:

what is your path_list ? Is it a list In that case you have to use [[ for extraction — akrun
– akrun, Commented Jun 18, 2018 at 20:28
@akrun Yes this is a list of characters. Where would I use the [[ for extraction? I was using the contents as an iterator through the for loop unsure how to restructure that. — rmahesh
– rmahesh, Commented Jun 18, 2018 at 20:29
Please use a reproducible example as it becomes easier to understand where the problem is. — akrun
– akrun, Commented Jun 18, 2018 at 20:30
@akrun I have included edit. If you need more details please let me know. The list contains those values and some. — rmahesh
– rmahesh, Commented Jun 18, 2018 at 20:32
@akrun All I had to do was transpose the list in which I held 'Iteration'. Thank you again. Your solution worked great! — rmahesh
– rmahesh, Commented Jun 19, 2018 at 15:08

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

We can wrap the $var in a single quote

library(sqldf)
fn$sqldf("Select Iteration, (SUM(Hours) / SUM(Effort)) as Efficiency
 From df WHERE Iteration = '$var2'")

Using a reproducible example

fn$sqldf("select cyl, (sum(hp)/sum(wt)) as Efficiency from mtcars where new = '$var2'")
#    cyl Efficiency
#1   6   34.21462

If we are doing this on a loop

path_list <- c("Mazda RX4", "Datsun 710", "Valiant")
for(var1 in path_list) print(sqldf(sprintf("select cyl, (sum(hp)/sum(wt)) as Efficiency from mtcars where new = '%s'", var1)))
#cyl Efficiency
#1   6   41.98473
#  cyl Efficiency
#1   4   40.08621
#  cyl Efficiency
#1   6   30.34682

data

data(mtcars)
mtcars$new <- row.names(mtcars)
var2 <- "Hornet 4 Drive"

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jun 18, 2018 at 20:17

akrun

891k38 gold badges590 silver badges700 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

rmahesh Over a year ago

That worked perfectly thank you so much. For some reason, this isn't working in the for loop I included in the edit. I used the same logic and it is giving me 'Error in tcltk::as.tclObj(X) : cannot handle object of mode 'list''.

Collectives™ on Stack Overflow

How to include a user defined variable in sqldf query?

1 Answer 1

data

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

data

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related