2

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!

Roger-123
  • 2,232
  • 1
  • 13
  • 33
stats_noob
  • 5,401
  • 4
  • 27
  • 83
  • a working example of the final query that you're trying to create would be helpful. As is, it's a bit confusing as to what you're trying to do in the last code segment. – Roger-123 Feb 01 '23 at 15:50

0 Answers0