CODEWITHSUNDEEP
r
Pal D
Pal D

Reputation: 1

How to get data from sql limited to the rows in dataframe in R

I have created a dataframe which has some 2K accounts.

The sample data looks like this - 

Acct ID
1002
1005
1007
1009

I have odbc connection and i want to get usage data which looks like following

Acct ID      Start Date      End Date     Usage
1001         10/1/2019      11/01/2019    100
1001         11/01/2019     12/01/2019    120
1002         02/02/2019     03/01/2019    300
1002         03/02/2019     04/01/2019    450
1003         05/10/2018     06/09/2018    225
1004         07/15/2018     08/13/2018    100
1005         11/12/2019     12/11/2019     50
1006         
1007
1008
1009

However to get this data the sql pull will take a long time. I want to limit it to the accounts in my dataframe. Basically it should get info for these accounts only - 1002,1005,1007,1009

How would I do this

Upvotes: 0

Views: 160

Answers (1)

Benjamin
Benjamin

Reputation: 17359

You can build your WHERE clause using the vector of account IDs.

query <- 
  paste0("SELECT * FROM [TABLE] WHERE [AcctID] IN (", 
         paste0(Dframe$AcctID, collapse = ", "), ")")

query
[1] "SELECT * FROM [TABLE] WHERE [AcctID] IN (1002, 1005, 1007, 1009)"

This will work for numeric values in the WHERE clause. If you need to filter by strings, you can do a similar pattern, but need to 1) insert single quotes around the values, and 2) evaluate your risk of SQL Injection attacks.

Parameterized queries are useful here, but the code looks a bit more heinous. This should work for both numeric and string WHERE clauses.

library(DBI)

conn <- dbConnect(...) # make your ODBC connection

Dframe <- data.frame(AcctID = c(1002, 1005, 1007, 1009))


param_list <- lapply(Dframe$AcctID, identity)
names(param_list) <- sprintf("x%s", seq_along(Dframe$AcctID))

query <- paste0("SELECT * FROM [TABLE] WHERE [AcctID] IN (", 
                paste0(sprintf("?%s", names(param_list)), collapse = ", "), ")")

Out <- 
  dbGetQuery(
    conn,
    sqlInterpolate(
      conn,
      query, 
      .dots = param_list
    )
  )

Upvotes: 1

Related Questions