On 2015-08-17 08:15 PM, Petite Abeille wrote:
>> On Aug 17, 2015, at 8:08 PM, 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.
> Hmmm?!?
>
> create table foo as
>
> with
> DataSet( position )
> as
> (
> select 1 as position
> union all
> select DataSet.position + 1 as position
> from DataSet
> where DataSet.position < 10
> )
> select *
> from DataSet;
>
> select * from foo;
Right you are, thanks for pointing it out - as long as the CTE follows
the CTA and not precedes it, this script demonstrates, in case anyone
was following:
-- Processing SQL in: D:\Documents\SQLiteAutoScript.sql
-- Script Items: 5 Parameter Count: 0 SQLitespeed v2.1
-- 2015-08-17 20:11:50.524 | [Info] Script Initialized,
Started executing...
--
================================================================================================
DROP TABLE IF EXISTS table_of_integers;
CREATE TEMP TABLE table_of_integers AS
WITH generate AS (
SELECT 1 AS seqno UNION ALL SELECT seqno+1 FROM generate
WHERE seqno<10
)
SELECT seqno FROM generate ORDER BY seqno;
WITH RECURSIVE generate AS (
SELECT 20 AS seqno UNION ALL SELECT seqno+5 FROM generate WHERE
seqno<100
)
INSERT INTO table_of_integers SELECT seqno FROM generate ORDER BY seqno;
SELECT * FROM table_of_integers;
-- seqno
-- ------------
-- 1
-- 2
-- 3
-- 4
-- 5
-- 6
-- 7
-- 8
-- 9
-- 10
-- 20
-- 25
-- 30
-- 35
-- 40
-- 45
-- 50
-- 55
-- 60
-- 65
-- 70
-- 75
-- 80
-- 85
-- 90
-- 95
-- 100
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
