I am trying to execute a query or the form:
SELECT a.col1, b.col2 FROM tbl1 a, tbl2 b
WHERE a.col2 = b.col1
AND a.col3 = df$x
AND a.col4 = df$y
On an Oracle database using RJDBC. I have a data frame with columns x and y which identify. I can't find any details on how to generate and execute the query for the individual values in my data frame.
My data frame is of the form:
df <- data.frame(x=c("LIB258", "LIB258", "LIB359"), y=c("A01", "B03", "C08"))
So I need a way to generate and sequentially run the three different queries, then collect the results into a data frame.
Is there an elegant way to do this in R? I know I can use a bit of paste
magic to build the individual queries, but then how do I run them and return the results to a dataframe?
Thanks Hassan
using plyr and paste
library(plyr)
dff <- data.frame(x=c("LIB258", "LIB258", "LIB359"), y=c("A01", "B03", "C08"))
ddply(dff,.(x,y),
function(rr){
query <- paste("SELECT a.col1, b.col2 FROM tbl1 a, tbl2 b ",
"WHERE a.col2 = b.col1",
paste("AND a.col3 = ",rr$x,sep=''),
paste("AND a.col4 = ",rr$y,sep=''),sep ='\n')
result <- dbGetQuery(conn, query)
})
this should retuen something like :
x y col1 col2
1 LIB258 A01 4 3
2 LIB258 A01 3 2
3 LIB258 A01 1 1
4 LIB258 B03 1 3
5 LIB258 B03 4 1
6 LIB258 B03 3 4
7 LIB359 C08 6 5
8 LIB359 C08 3 1
9 LIB359 C08 1 4