CODEWITHSUNDEEP
sqlr
Learner_seeker
Learner_seeker

Reputation: 544

Using R object in DBgetquery

How do i use an R object in a DBgetquery ?

example

i have some selected ids in R as topIds topIds=c('a','b','c'......)

i want to query details of only these topIDs

query =dbgetquery(conn, " SELECT AGE , NAME  
                          FROM table 1
                          where ID= paste0('topIds') )

how do i put topIds R object into the query to fetch my info ?

Upvotes: 1

Views: 1983

Answers (2)

Alexvonrass
Alexvonrass

Reputation: 330

You need to collapse topIds and paste0 it into the query, something like this

ids_for_query <- paste0(topIds, collapse = ',')
query <- paste0("SELECT AGE , NAME
FROM table 1 where ID IN(", ids_for_query, ")", sep = '')

You might however encounter a problem if topids is very large

Upvotes: 0

Marmite Bomber
Marmite Bomber

Reputation: 21115

For a brief list of variables (in Oracle less then 1000 keys) you may use paste

For numeric key simple paste with delimiter ,

> numLst <- c(1,2,3)
> paste('select * from T where id in (',paste(numLst, collapse=','),')')
[1] "select * from T where id in ( 1,2,3 )"

For character key you must additionaly quote the strings:

> charLst <- c('a','b','c')
> quotedLst <- mapply(paste0, "'", charLst, "'")
> paste('select * from T where id in (',paste(quotedLst, sep="'", collapse=','),')')
[1] "select * from T where id in ( 'a','b','c' )"

If you have a large number of keys you may put them in a database table first (check dbWriteTable or sqlSave) and constrain the key direct in the DB query.

Upvotes: 2

Related Questions