2

Question -

how to use apply function or for loop to run this query below over different values

run query 

dbgetQuery(conn, " SELECT ID , Name , Date , Product 
                   FROM table xyz
                   where Year = i ")

where i is years ( 2010 to 2016 )

how to get table.2011 = data for 2011( IDs , Names , Dates , Product) table.2012 = data for 2012( IDs , Names , Dates , Product) and so on..for each year.

r2evans
  • 141,215
  • 6
  • 77
  • 149
Learner_seeker
  • 544
  • 1
  • 4
  • 21
  • What type of database are you using? (This drives how to do parameterized queries, since all databases are different in DBI.) There are plenty of questions on SO related to "lists of data.frames", they are very appropriate here: since `dbGetQuery` should return a data.frame, the methodology will be the same. (This is a strong suggestion *against* having `table.2011` and `table.2012`, instead going for a `list` of data.frames.) – r2evans Feb 24 '17 at 05:52
  • ok how to get list of data.frames in this question. SQL Db , connected via JDBC – Learner_seeker Feb 24 '17 at 06:02
  • `sapply(2010:2016, function(i) dbGetQuery(conn, "..."))`. You should not be inserting the value directly into the query string, instead you should use a [parameterized queries](http://stackoverflow.com/questions/4712037/what-is-parameterized-query). Unfortunately, *all databases do it differently*; `RPostgres` uses `$1`, `RSQLite` uses `:varname`, `RSQLServer` uses `?1`, etc. You said *"SQL Db"* which does nothing to narrow it down. *"What type of database are you using?"* (continued ...) – r2evans Feb 24 '17 at 06:17
  • As far as working on a list of data.frames, perhaps this would be helpful: http://stackoverflow.com/questions/29721283/making-multiple-named-data-frames-with-loop/29722867#29722867 – r2evans Feb 24 '17 at 06:27

1 Answers1

1
year <- factor(c(2010,2011,2012,2013,2014,2015,2016))

for(i in levels(year))

{

assign(paste0('table.',i),dbgetQuery(conn, paste("SELECT ID , Name , Date , Product FROM table xyz where Year = '",i,"';")))

}
toha
  • 5,095
  • 4
  • 40
  • 52
Sheeja Mathai
  • 19
  • 4