RJDBC Query from lists of index values

Question

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

score 2 · Answer 1 · answered Nov 26 '12 at 17:23

2

I don't know RJDBC, but I have used RODBC. You can use a bit of paste or paste0 to create the queries:

query.list <- paste0("
  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)

Then use lapply, something like

result.set <- lapply(query.list,dbGetQuery,conn=con)

This should return a list of data frames. Then you can join them all back together with

final.df <- do.call(rbind,result.set)

answered Nov 26 '12 at 17:23

Blue Magister

13,044
5
38
56

I also noticed that although this solution has extra steps, by keeping `result.set` separate, you can check it for any missing values before creating the final data.frame! – Hassantm Nov 28 '12 at 12:19

score 2 · Accepted Answer · answered Nov 26 '12 at 17:51

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

Thanks, that's exactly what I was after. For reference and anyone else who finds this, I should have said that the keys are strings so need to be quoted, but that's easily done by making the `paste` statement read `paste("AND a.col3 = ", shQuote(rr$x), sep='')` etc. — Hassantm, Nov 28 '12 at 12:03
Note that `paste0` is a slightly more efficient version of `paste` with `sep = ''`. — Blue Magister, Dec 03 '12 at 01:26

RJDBC Query from lists of index values

2 Answers2