CODEWITHSUNDEEP
sqlrdb2
brucezepplin
brucezepplin

Reputation: 9752

store results of looped sqlquery function

Hi I am using R and the sqlquery function, and I cannot seem to store the results of looping through the sqlquery function into a data frame.

I have:

year<- seq(2000,2010)

for (i in 1:length(year)){

sqlQuery(channel,paste("

select * from table where year = '",year[i],"-01-01'

",sep-""))
}

however doing:

results <- for (i in 1:length(year)){
}

does not store anything. athough I can store the single result of year = i by doing

results <- sqlQuery(channel,paste("

Which is obviously not what I want - I want all years. Where am I going wrong?

Upvotes: 0

Views: 70

Answers (1)

Jonas Tundo
Jonas Tundo

Reputation: 6197

Difficult to answer because it's not a reproducible example, but maybe something like:

results <- list() 
year<- seq(2000,2010)

for (i in 1:length(year)){

results[[i]]<-sqlQuery(channel,paste("

select * from table where year = '",year[i],"-01-01'

",sep-""))
}

The datasets are saved in the object results, which is a list of 11 datasets. To call the first dataset with data of the year 2000, you do results[[1]].

To unlist and produce the different dataframes you could do:

for (i in 1:length(year)){
assign(paste0("results",year[i]),results[[i]])
}

Dataframe result2000 contains the data of the year 2000 and so on.

Upvotes: 1

Related Questions