Search code examples
sqlrsqlitersqlite

R dbGetQuery with dynamic string

From This post and This post, I got a way to write an rsqlite dynamic command. However, it doesn't work for me. My data looks like:

Id <- c(34, 22, 86)
sqlcmd <- paste("select col1, col2 from DB where ItemId =", Id, sep="")
Df <- dbGetQuery(conn, sqlcmd)

My sqlcmd gives me a list of strings as 

"select col1, col2 from DB where STOREID =34"
"select col1, col2 from DB where STOREID =22"
"select col1, col2 from DB where STOREID =86"

However, when I pass sqlcmd to dbGetQuery, it only returns data with ItemId = 34, which is the first element in the Id list.

I'm wondering if anyone has any ideas on why does this happen? Any help would be appreciated!

Solution

  • Since I believe R DBI drivers have not yet implemented multiple SQL statements support, dbGetQuery only returns first statement.

    Hence, you need to iteratively run your sqlcmd for multiple SQL statements such as with lapply to return a list of dataframes, followed by an rbind call for single master dataframe: 

    Id <- c(34, 22, 86)
sqlcmd <- paste("select col1, col2 from DB where ItemId =", Id, sep="")

# LIST OF DATAFRAMES
df_list <- lapply(sqlcmd , function(x) dbGetQuery(conn, x)) 

# FINAL DATAFRAME
final_df <- do.call(rbind, df_list)

    Alternatively, use a UNION or UNION ALL for one SQL statement. 

    Id <- c(34, 22, 86)
sqlcmd <- paste("select col1, col2 from DB where ItemId =", Id, sep="")

single_sql <- paste(sqlcmd, collapse = " UNION ")
final_df <- dbGetQuery(conn, single_sql)

    Or still use OR:

    single_sql <- paste("select col1, col2 from DB where ItemId =", 
                    paste(Id, collapse=" OR ItemId = "))

final_df <- dbGetQuery(conn, single_sql)