roracle-databasejdbcrjdbc

R DBI::dbGetQuery - adding multiple values within IN from a vector

library(RJDBC)
library(odbc)
library(tidyverse)
library(writexl)
library(readxl)

Sample vector for the IN clause

values_for_in_clause <- c("23545234", "3424566", "11245677")

Comma-separated string for the IN clause

in_clause_values <- paste0("'", values_for_in_clause, "'", collapse = ",")


data <- DBI::dbGetQuery(Connection, 
                                 "
SELECT numeric_column
FROM data 
WHERE    numeric_column IN (", in_clause_values, ")
")

I get this error:

Error in .jcall(s, "V", "setString", i, as.character(v)) :
java.sql.SQLException: ORA-17003: Invalid column index

The code should look like this:

SELECT numeric_column
FROM data 
WHERE    numeric_column IN ('23545234', '3424566', '11245677')
Solution

  • Combine sQuote with toString to format a vector for use in a SQL statement:

    library(odbc)
library(tidyverse)
    
values <- c("23545234", "3424566", "11245677")

formatted_values <- toString(sQuote(values, q = F))

query <- paste0("select numeric_column
from data
where numeric_column in (", formatted_values, ");")

data <- dbGetQuery(conn = connection,
                   statement = query)