Srivathsan V Srivathsan V - 3 months ago 21
SQL Question

Parameterized SQL query in R with IN clause

I am trying to fetch data through RODBC package from Vertica DB. I currently have a SQL query like the one below.

library(rodbc) channel = odbcconnect("VerticaDB") query = paste
(
SELECT *
FROM item_history
WHERE item_exp_date BETWEEN ",x," AND ",y,"
AND item_code IN ('A1',
'A2',
'B1',
'B2')",sep="")result = (sqlQuery(channel,query)
)


I have been able to parameterize the data passed in the 'BETWEEN' clause. Is there a way i can parameterize data being passed in the 'IN' clasue?

Also the number of data elements being passed in the 'IN' clause is very high (over 100 distinct items).

Is there a way if it can be passed from an external Vector or a file?

Answer

To do this with string manipulation as in the question:

x <- "2000-01-01"
y <- "2001-01-01"
Item_Code <- c('A1','A2','B1','B2')

query <- sprintf("select * from Item_History
                  where Item_Exp_Date between '%s' and '%s'
                        and Item_Code in (%s)", x, y, toString(shQuote(Item_Code, 'sh')))

We could alternately use fn$ from the gsubfn package for string interpolation:

library(gsubfn)
query2 <- fn$identity("select * from Item_History
              where Item_Exp_Date between '$x' and '$y'
              and Item_Code in ( `toString(shQuote(Item_Code, 'sh'))` )")