CODEWITHSUNDEEP
sqlroracle-database
jvalenti
jvalenti

Reputation: 640

R - use R code as string in Oracle SQL Query

I am attempting to execute an Oracle SQL query like so:

library("data.table")
library("RODBC")
db = "database"
uid = "my username"
pw = "my password"
lookup = "12345D%"

myconn <- odbcConnect(db, uid, pw)

query_df <- as.data.table(sqlQuery(
    myconn,
    "SELECT *
     FROM tableX
     WHERE tableX.code LIKE '12345D%';"
))
close(myconn)

My issue is that it is redundant to define lookup before the query and use the value in the query itself. I would like to just be able to type

WHERE tableX.code LIKE lookup

and have the value 12345D% substituted into my query. I tried substituting lookup into the query directly but I received a result with no observations. Of course it works fine when I put 12345D% into the query itself but I would prefer to simply change the stored value before executing the query since I will have several of these queries to run using that value lookup.

Is there a way to do this? Any advice would be much appreciated. Thanks.

Upvotes: 0

Views: 148

Answers (1)

Waldi
Waldi

Reputation: 41220

You could use the glue package which allows to easily embed R expressions in the query string using curly braces :

lookup <- '12345D%'

(qry <- glue::glue("SELECT *
     FROM tableX
     WHERE tableX.code LIKE '{lookup}';"))

# SELECT *
# FROM tableX
# WHERE tableX.code LIKE '12345D%';

query_df <- as.data.table(sqlQuery(myconn, qry))

Upvotes: 1

Related Questions