[sqlite] Enhance the SELECT statement?

[Kees Nuyt]

On Mon, 17 Aug 2015 20:08:23 +0200, "R.Smith"
<rsmith at rsweb.co.za> wrote:

> CORRECTION: It seems one of the two options I've mentioned earlier, 
> namely the CREATE TABLE AS SELECT... does not actually work on the back 
> of a WITH clause. The other option still do, but this request has more 
> appeal now.

CREATE TABLE works if you swap the order of CREATE and WITH:

$ cat ~/sql/test.sql
.head on
.echo on
-- syntax error
WITH RECURSIVE generate AS
    ( SELECT 1 AS seqno UNION ALL SELECT seqno+1 FROM generate
WHERE seqno < 10)
CREATE TABLE table_of_i1 AS SELECT seqno FROM generate ORDER BY
seqno;

-- accepted
CREATE TABLE table_of_i2 AS
 WITH RECURSIVE generate AS
    ( SELECT 1 AS seqno UNION ALL SELECT seqno+1 FROM generate
      WHERE seqno < 10)
 SELECT seqno FROM generate ORDER BY seqno;
PRAGMA table_info(table_of_i2);

-- alternative syntax
CREATE TABLE table_of_i3 (seqno INTEGER PRIMARY KEY NOT NULL);
WITH RECURSIVE generate AS
    ( SELECT 1 AS seqno UNION ALL SELECT seqno+1 FROM generate
WHERE seqno < 10)
INSERT INTO table_of_i3 (seqno) SELECT seqno FROM generate ORDER
BY seqno;
PRAGMA table_info(table_of_i3);

.dump

-- 
Regards, Cordialement, Groet,

Kees Nuyt