0

I am reading list of values from CSV file in R, and trying to pass the values into IN condition of SQL(dbGetQuery). Can some one help me out with this?

library(rJava)
library(RJDBC)
library(dbplyr)
library(tibble)
library(DBI)
library(RODBC)
library(data.table)


jdbcDriver <- JDBC("oracle.jdbc.OracleDriver",classPath="C://Users/********/Oracle_JDBC/ojdbc6.jar")

jdbcConnection <- dbConnect(jdbcDriver, "jdbc:oracle:thin:Rahul@//Host/DB", "User_name", "Password") 

## Setting working directory for the data
setwd("C:\\Users\\**********\\Desktop")

## reading csv file into data frame
pii<-read.csv("sample.csv")

pii

 PII_ID
S0094-5765(17)31236-5
S0094-5765(17)31420-0
S0094-5765(17)31508-4
S0094-5765(17)31522-9
S0094-5765(17)30772-5
S0094-5765(17)30773-7

PII_ID1<-dbplyr::build_sql(pii$PII_ID)

PII_ID1

<SQL> ('S0094-5765(17)31236-5', 'S0094-5765(17)31420-0', 'S0094-5765(17)31508-4', 'S0094-5765(17)31522-9', 'S0094-5765(17)30772-5', 'S0094-5765(17)30773-7')


Data<-dbGetQuery(jdbcConnection, "SELECT ARTICLE_ID FROM JRBI_OWNER.JRBI_ARTICLE_DIM WHERE PII_ID in  ?",(PII_ID1))

Expected: 

ARTICLE_ID
12345
23456
12356
14567
13456

Actual result:

[1] ARTICLE_ID
<0 rows> (or 0-length row.names)
zx8754
  • 52,746
  • 12
  • 114
  • 209
rahul voruganti
  • 1
  • 1
  • 1
    can you do `query = paste0("SELECT ARTICLE_ID FROM JRBI_OWNER.JRBI_ARTICLE_DIM WHERE PII_ID IN ", PII_ID1)` and then try `Data<-dbGetQuery(jdbcConnection, query)` ? – Ronak Shah Apr 08 '19 at 10:01
  • 1
    In general, when using the `IN` condition, I'd prefer to operate a join. Use `dbWriteTable` to create a temporary table with the `PII_ID` values and then operate a join. In this way you don't need to build and send huge queries. – nicola Apr 08 '19 at 10:22

1 Answers1

1

The SQL code you pass in the second argument to dbGetQuery is just a text string, hence you can construct this using paste or equivalents.

You are after something like the following:

in_clause <- paste0("('", paste0(pii$PII_ID, collapse = "', '"), "')")

sql_text <- paste0("SELECT ARTICLE_ID 
              FROM JRBI_OWNER.JRBI_ARTICLE_DIM
              WHERE PII_ID IN ", in_clause)

 data <- dbGetQuery(jdbcConnection, sql_text)

However, the exact form of the first paste0 depends on the format of PII_ID (I have assumed it is text) and how this format is represented in sql (I have assumed single quotes).

Be sure to check sql_text is valid SQL before passing it to dbGetQuery.

IMPORTANT: This approach is only suitable when pii contains a small number of values (I recommend fewer than 10). If pii contains a large number of values your query will be very large and will run very slowly. If you have many values in pii then a better approach would be a join or semi-join as per @nicola's comment.

Simon.S.A.
  • 6,240
  • 7
  • 22
  • 41