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, the second using the first one, soI tried to "copy" my first named query to work around the errors I was getting, but still no luck. What am I missing? I'm having a hard time wrapping my head around CTEs... I was trying to use a CTE to split multi-column rows into 2 rows per initial row, first half of columns in first new row, then second half of columns in another new row, w/o repeating the (longuish) query yield the rows to split in the first place. Can CTEs do that? Thanks, --DD C:\Users\DDevienne>sqlite3 SQLite version 3.8.3.1 2014-02-11 14:52:19 Enter ".help" for instructions Enter SQL statements terminated with a ";" 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), ...> cpy(b) as (select a from cte) ...> select a from cte ...> union all ...> select b from cpy; Error: no such table: cte sqlite> with recursive ...> cte(a) as (select 1), ...> cpy(b) as (select a from cte) ...> select a from cte ...> union all ...> select b from cpy; Error: no such table: cte _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
