Re: [sqlite] CTEs and unions

> On 4/22/2014 5:55 PM, Andy Goth wrote: >> On 4/22/2014 5:16 PM, Dominique Devienne wrote: >>> sqlite> with cte(a) as (select 1) >>> ...> select * from cte >>> ...> union all >>> ...> select * from cte; >>> Error: no such table: cte >> > All these queries work for me without error. >

Re: [sqlite] CTEs and unions

On 4/22/2014 5:55 PM, Andy Goth wrote: On 4/22/2014 5:16 PM, Dominique Devienne wrote: sqlite> with cte(a) as (select 1) ...> select * from cte ...> union all ...> select * from cte; Error: no such table: cte All these queries work for me without error.

Re: [sqlite] CTEs and unions

On 4/22/2014 5:16 PM, Dominique Devienne wrote: sqlite> with cte(a) as (select 1) ...> select * from cte; a 1 sqlite> with cte(a) as (select 1) ...> select * from cte ...> union all ...> select * from cte; Error: no such table: cte sqlite> with cte(a) as (select 1), ...>

Re: [sqlite] CTEs and unions

On Apr 23, 2014, at 12:16 AM, Dominique Devienne wrote: > is there no way to reuse a CTE several times? Hrm… of course you can… that’s the entire point of *Common* Table Expression: with DataSet as ( select 1 as value ) select * fromDataSet union all select * from

[sqlite] CTEs and unions

so we can't use CTEs to avoid stuttering in queries? This is a contrived example of course, but is there no way to reuse a CTE several times? In this case, to get two rows of one column, both 1s, without repeating the query? https://sqlite.org/lang_with.html shows a CTE with two named queries,