CODEWITHSUNDEEP
sqlr
Kate N
Kate N

Reputation: 453

How to use character string in R inside SQL WHERE IN statement

I would like to use a string of character IDs in the WHERE clause of SQL query. My id object looks like this

ids
 [1] "0000000000000000010000001" "0000000000000000010000002" "0000000000000000010000003"
 [4] "0000000000000000010000004" "0000000000000000010000005" "0000000000000000010000006"
 [7] "0000000000000000010000007" "0000000000000000010000008" "0000000000000000010000009"
[10] "0000000000000000010000010"

I would like to insert that object into this SQL query:

student <- sqlQuery(con,
  "select
    FSAS.a_id,
    FSAS.grade,
    FSAS.score,
    FSAS.placement,
    FSAS.start,
    FSAS.completion

    FROM db.Fact AS FSAS
      WHERE FSAS.a_id IN ids"

)

I think the entire SQL query has to be a string but I can't figure out how to get it right.

Upvotes: 2

Views: 699

Answers (2)

akrun
akrun

Reputation: 887088

We can use paste to create the expression

expr1 <- sprintf("select
 FSAS.a_id,
 FSAS.grade,
 FSAS.score,
 FSAS.placement,
 FSAS.start,
 FSAS.completion 
 FROM db.Fact AS FSAS
   WHERE FSAS.a_id IN (%s)", paste0(sQuote(ids, q = FALSE), collapse=", "))

sqlQuery(con, expr1)

Upvotes: 4

Kate N
Kate N

Reputation: 453

This should work:

qry = paste("select
    FSAS.a_id,
    FSAS.grade,
    FSAS.score,
    FSAS.placement,
    FSAS.start,
    FSAS.completion

    FROM db.Fact AS FSAS
      WHERE FSAS.a_id IN", ids, sep = "")

student <- sqlQuery(con,
     qry
)

Upvotes: 0

Related Questions