Referencing a Query in Another Query-CodePudding

I have this table on a server:

library(DBI)   
con <- dbConnect(RSQLite::SQLite(), ":memory:")  
dbWriteTable(con, "iris", iris)

With the following code, I can define a CTE and then execute this CTE in the same query:

dbGetQuery(
  con,
  statement = "
    WITH CTE_1 AS (SELECT * FROM iris WHERE (Species = 'setosa') ORDER BY RANDOM())
    ,CTE_2 AS (SELECT * FROM iris WHERE (Species = 'versicolor') ORDER BY RANDOM())

SELECT
  (SELECT count(*) from CTE_1) as A
  ,(SELECT count(*) from CTE_2) as B;"
)

I was wondering if it is possible to define these CTE's in advance (separately), and then call them in a future query?

For example:

CTE_1 = paste("SELECT * FROM iris WHERE (Species = 'setosa') ORDER BY RANDOM())", sep="")
CTE_2 = paste("SELECT * FROM iris WHERE (Species = 'setosa') ORDER BY RANDOM())" sep="")

Is it possible to then reference these into a future query? Something like this:

dbGetQuery(
  con,
  "SELECT
  (SELECT count(*) from CTE_1) as A
  ,(SELECT count(*) from CTE_2) as B;"
)

Thank you!

CodePudding user response：

Common Table Expressions (CTE) only "survive" within the query that defines them. Once that query is finished the CTE of that query cannot be referenced.

For persistent queries you could use views instead e.g.

create view iris_setosa as 
select col1, col2, col3
from iris
where Species = 'setosa';

create view iris_versicolor as 
select col1, col2, col3
from iris
where Species = 'versicolor';

Then some time later:

select
       (count(*) from iris_setosa) as setosa
     , (count(*) from iris_versicolor) as versicolor

But these counts could easily be achieved this way:

select
       count(case when species = 'setosa' then 1 end) as setosa
     , count(case when species = 'versicolor' then 1 end) as versicolor
from iris
where species in ('setosa','versicolor')

nb: Do avoid "select *" it isn't "best practice".

CodePudding user response：

for this scenario you don't need CTE, a variable or a function() should be enough.

cnt1 <- dbGetQuery(con,
                  statement = "SELECT * FROM iris WHERE (Species = 'setosa') ORDER BY RANDOM()                  
                )
                
cnt2 <- dbGetQuery(con,
                  statement = "SELECT * FROM iris WHERE (Species = 'versicolor') ORDER BY RANDOM()                  
                )