rr-dbi

How can I run dbGetQuery in a loop?

I am running an R script that extract data from oracle table using the defined condition in the where clause.

library(DBI)
DF <- dbGetQuery(Con, "select table_name, Last_updated from Summary_table
                       where type in ('Prime', 'Secondary')

The output from the above statement sends out to users via mailR command. I want to update the above statement to run for each 'type' in the where clause as individual statement i.e first run where type in 'Prime' and sends email out and then where type in 'Secondary' and sends out an email. Rather than running as an individual, I am wondering if I can create a loop. Any suggestions on this. I am not sure if I can pass a parameter in the query.

Solution

  • You can parameterize your query, i.e.

    dbGetQuery(
  con,
  "SELECT COUNT(*) FROM mtcars WHERE cyl = ?",
  params = list(1:8)
)
#>   COUNT(*)
#> 1        0
#> 2        0
#> 3        0
#> 4       11
#> 5        0
#> 6        7
#> 7        0
#> 8       14

    See here for full explanation.