apply or For loop in SQL query in R

Question

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.

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
– r2evans, Commented Feb 24, 2017 at 5:52
ok how to get list of data.frames in this question. SQL Db , connected via JDBC — Learner_seeker
– Learner_seeker, Commented Feb 24, 2017 at 6: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. 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
– r2evans, Commented Feb 24, 2017 at 6:17
As far as working on a list of data.frames, perhaps this would be helpful: stackoverflow.com/questions/29721283/… — r2evans
– r2evans, Commented Feb 24, 2017 at 6:27

toha · Accepted Answer · 2017-07-28 07:02:10Z

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,"';")))

}

edited Jul 28, 2017 at 7:02

toha

5,5354 gold badges43 silver badges56 bronze badges

answered Jul 28, 2017 at 6:24

Sheeja Mathai

194 bronze badges

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

Collectives™ on Stack Overflow

apply or For loop in SQL query in R

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related