0

I have the following code that works pretty smoothly:

 sql_cag <- sprintf("SELECT * FROM `CAG_table` WHERE `comp_id` = 1");
 df <- dbGetQuery(con, sql_cag)

What I would like to do now, however, is to replace the 1 by a parameter. So like this: 

 sql_cag <- sprintf("SELECT * FROM `CAG_table` WHERE `comp_id` ='%a'", competitie_id);
 df <- dbGetQuery(con, sql_cag)

With a parameter competitie_id

 competitie_id <- 1
 competitie_id <- as.numeric(competitie_id)

But this returns an empty dataframe. So somehow, something seems to go wrong with the '%a'. Any thoughts where this might go wrong?

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
Frits Verstraten
  • 2,049
  • 7
  • 22
  • 41
  • 1
    Your first SQL statement compares comp_id to 1 whereas the second compares it to '0x1p+0' assuming that competitie_id is 1. Use %d, not %a and remove the single quotes. You could also use fn$ in the gsubfn function which allows quasi-perl style string interpolation. – G. Grothendieck Mar 31 '16 at 12:30
  • @G.Grothendieck that works. Thanks! – Frits Verstraten Mar 31 '16 at 13:16

0 Answers0