Defining CTE (Common Table Expressions) in R?

Question

I have this table on a server - I am querying it with R:

library(dplyr) 
library(DBI) 

con <- dbConnect(RSQLite::SQLite(), ":memory:") 

dbWriteTable(con, "iris", iris)

I was able to run this query:

DBI::dbGetQuery(con, " select *, row_number()  OVER ( ORDER BY Species) as rn
                        from iris
                        where Species = 'setosa'")

I would like to convert this query into a CTE - but I am not sure how to do this (I think some sort of sql-wrapper might need to be placed around this?):

#does not work
with cte_setosa as (select *, row_number()  OVER ( ORDER BY Species) as rn
                    from iris
                    where Species = 'setosa')

In the end, I would try to "call" the CTE like this:

library(glue)
setosa_sample_vector <- glue_sql(paste0("(", paste(sample(1:50, 30, replace = T), collapse = "),("), ")"), .con = con)

DBI::dbGetQuery(con, " select iris.*
from (values ?setosa_sample_vector) sv
left join cte_setosa as base on iris.rn = sv.column1 ;")

Can someone show me how CTE's can be defined in R/SQL?

Thank you!

Defining CTE (Common Table Expressions) in R?

0 Answers