Replace multiple variables in R Sql Query-CodePudding

I have a query similar like the one below where I need to use multiple string of characters to replace in a single query.

I am not too sure how to make sure a and b are passed into the %s in the query.

I am getting the error 'unused arguments (b)'

   a
 [1] "A10000001" "C10000002" "10000003"
 [4] "10000004" "D10000005" "10000006"
 


  b
 [1] "A100000011" "B100000021" "100000031"
 [4] "10000004" "10000005" "10000006"

expr1 <- sprintf("select
 FSAS.a_id,
 FSAS.grade,
 FSAS.score,
 FSAS.placement,
 FSAS.start,
 FSAS.completion 
 FROM db.Fact AS FSAS
 LEFT JOIN
 (SELECT FB.end , FB.ids, FB.place FROM FB where FB.ids in (%s))
 ON FB.end = FSAS.start
 LEFT JOIN
 (SELECT FAB.a, FAB.ids FROM FAB WHERE FAB.ids in (%s))
 ON FAB.a = FB.place", toString(sQuote(a, b, q = FALSE), collapse=", "))

sqlQuery(con, expr1)

Any advice please?

Thanks

CodePudding user response：

sQuote only takes one vector as an argument. You can't give it a and b at the same time as separate arguments. And collapse is an argument for paste, not for toString. And sprintf will want separate arguments for each %s. I think you want

qry = "select
 FSAS.a_id,
 FSAS.grade,
 FSAS.score,
 FSAS.placement,
 FSAS.start,
 FSAS.completion 
 FROM db.Fact AS FSAS
 LEFT JOIN
 (SELECT FB.end , FB.ids, FB.place FROM FB where FB.ids in (%s))
 ON FB.end = FSAS.start
 LEFT JOIN
 (SELECT FAB.a, FAB.ids FROM FAB WHERE FAB.ids in (%s))
 ON FAB.a = FB.place"

expr1 = sprintf(
  qry,
  toString(sQuote(a, q = FALSE)),
  toString(sQuote(b, q = FALSE))
)