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
Advertisement
Answer
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)) )