Recursive aggregate query?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Recursive aggregate query?

nomad
I don't understand the error message generated by the following
schema/query:

    CREATE TABLE x(
        id integer
    );

    CREATE TABLE y(
        id integer
    );

    CREATE TABLE y_sequence (
        seq INTEGER PRIMARY KEY AUTOINCREMENT
    );


    WITH
        x
    AS
        (SELECT
            0 AS name,
            0 as rows

          UNION ALL
           
        SELECT
            'sqlite_sequence' AS "name",
            COUNT(*) AS rows
        FROM
            sqlite_sequence

          UNION ALL
           
        SELECT
            'y_sequence' AS "name",
            COUNT(*) AS rows
        FROM
            y_sequence

          UNION ALL
           
        SELECT
            'y' AS "name",
            COUNT(*) AS rows
        FROM
            y

          UNION ALL
           
        SELECT
            'x' AS "name",
            COUNT(*) AS rows
        FROM
            x
        )
    SELECT
        sm.name AS name,
        x.rows AS rows,
        SUM(CASE
            WHEN
                sm2.type="index"
            THEN
                1
            ELSE
                0
        END) AS "indexes",
        SUM(CASE
            WHEN
                sm2.type="trigger"
            THEN
                1
            ELSE
                0
        END) AS "triggers",
        SUM(CASE
            WHEN
                sm2.type="trigger" AND sm2.sql LIKE "%BEFORE INSERT%"
            THEN
                1
            ELSE
                0
        END) AS "bi",
        SUM(CASE
            WHEN
                sm2.type="trigger" AND sm2.sql LIKE "%AFTER INSERT%"
            THEN
                1
            ELSE
                0
        END) AS "ai",
        SUM(CASE
            WHEN
                sm2.type="trigger" AND sm2.sql LIKE "%BEFORE UPDATE%"
            THEN
                1
            ELSE
                0
        END) AS "bu",
        SUM(CASE
            WHEN
                sm2.type="trigger" AND sm2.sql LIKE
                        "%AFTER UPDATE%"
            THEN
                1
            ELSE
                0
        END) AS "au",
        SUM(CASE
            WHEN
                sm2.type="trigger" AND sm2.sql LIKE "%BEFORE DELETE%"
            THEN
                1
            ELSE
                0
        END) AS "bd",
        SUM(CASE
            WHEN
                sm2.type="trigger" AND sm2.sql LIKE "%AFTER DELETE%"
            THEN
                1
            ELSE
                0
        END) AS "ad"
    FROM
        sqlite_master sm
      LEFT JOIN
        sqlite_master sm2
      ON
        sm2.tbl_name = sm.tbl_name
      LEFT JOIN
        x
      ON
        x.name = sm.tbl_name
    WHERE
        sm.tbl_name NOT LIKE ? AND sm.type = ?
    GROUP BY
        sm.name
    ORDER BY
        sm.name
    ;

    DBD::SQLite::db prepare failed: recursive aggregate queries not supported at /usr/lib/perl5/site_perl/5.22/DBIx/ThinSQL.pm line 250.


I don't quite see where the recursive aggregate is. Can anyone
enlighten me?

--
Mark Lawrence
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Recursive aggregate query?

nomad
On Tue Nov 28, 2017 at 10:13:56AM +0100, [hidden email] wrote:
> I don't understand the error message generated by the following
> schema/query:
>
>     CREATE TABLE x(
>         id integer
>     );
>
...
>
>     WITH
>         x
>     AS

Woops - I just realized the CTE uses a name already defined as a table.
Sorry for the noise.

--
Mark Lawrence
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users