[sqlite] Worked in 3.8.9, but now "recursive aggregate queries not supported"

[E.Pasma]

Op 12 sep 2015, om 13:48 heeft Aurel Wisse het volgende geschreven:
>
>
> Here is the example. It doesn't use an actual aggregation (sum,  
> count), but
> the
>
> "min" aggregate function.
>
>
> From a list of dates, create a lookup table with two
>
> columns: the original date (calc_date) and the date after calc_date  
> (tmw).
>
>
> CREATE TABLE offsets AS
>
> WITH RECURSIVE dtoff(calc_date, tmw) AS (
>
> WITH dates(cd) AS (SELECT DISTINCT calc_date FROM securities)
>
> SELECT NULL, min(cd) FROM dates
>
> UNION
>
> SELECT dtoff.tmw, min(dates.cd) FROM dtoff, dates
>
> WHERE dates.cd > dtoff.tmw AND dtoff.tmw NOTNULL
>
> AND strftime('%%w', dates.cd)<>'0')
>
> SELECT * from dtoff WHERE calc_date NOTNULL ORDER BY calc_date;
>


The query below should work.
I hope that that is not a matter of good luck?

CREATE TABLE securities(calc_date)
;
WITH RECURSIVE dtoff(calc_date, tmw) AS (

WITH dates(cd) AS (SELECT DISTINCT calc_date FROM securities)

SELECT NULL, min(cd) FROM dates

UNION

SELECT dtoff.tmw,
         -- begin change 150912
         (
     SELECT min(dates.cd)
     FROM dates
     WHERE dates.cd > dtoff.tmw
     AND strftime('%%w', dates.cd)<>'0'
         )
         -- end change 150912
FROM dtoff
WHERE dtoff.tmw NOTNULL
     )
SELECT * from dtoff WHERE calc_date NOTNULL ORDER BY calc_date;

~