WITH examples to look forward to...

[juandent]

Just to have them all in one place, I am placing the examples of the WITH clause in this issue as found in the document you shared with me the other day: (https://blog.expensify.com/2015/09/25/the-simplest-sqlite-common-table-expression-tutorial/)

WITH one AS ( SELECT 1 )
        SELECT * FROM one;

WITH twoCol( a, b ) AS ( SELECT 1, 2 )
        SELECT a, b FROM twoCol;

WITH fooCTE AS (SELECT * FROM foo)
        SELECT * FROM fooCTE;

WITH aCTE AS (SELECT 'a'), 
             bCTE AS (SELECT 'b')
        SELECT * FROM aCTE, bCTE;

WITH RECURSIVE finite AS ( 
            SELECT 1 
                UNION ALL 
            SELECT * FROM finite LIMIT 2 
        ) 
        SELECT * FROM finite;

WITH RECURSIVE ten(x) AS ( 
            SELECT 1 
                UNION ALL 
            SELECT x+1 FROM ten WHERE x<10 
        )
        SELECT * FROM ten;

WITH RECURSIVE dates(x) AS ( 
            SELECT '2015-01-01' 
                UNION ALL 
            SELECT DATE(x, '+1 MONTHS') FROM dates WHERE x<'2016-01-01' 
        ) 
        SELECT * FROM dates;

WITH RECURSIVE list( element, remainder ) AS (
            SELECT NULL AS element, '1,2,3,4,5' AS remainder
                UNION ALL
            SELECT
                CASE
                    WHEN INSTR( remainder, ',' )>0 THEN 
                        SUBSTR( remainder, 0, INSTR( remainder, ',' ) )
                    ELSE
                        remainder
                END AS element,
                CASE
                    WHEN INSTR( remainder, ',' )>0 THEN 
                        SUBSTR( remainder, INSTR( remainder, ',' )+1 )
                    ELSE
                        NULL
                END AS remainder
            FROM list
            WHERE remainder IS NOT NULL
        )
        SELECT element FROM list WHERE element IS NOT NULL;

WITH RECURSIVE approvers(x) AS (
            SELECT 'Joanie' 
                UNION ALL
            SELECT company.approver 
            FROM company, approvers 
            WHERE company.name=approvers.x AND company.approver IS NOT NULL
        )
        SELECT * FROM approvers;

May God help us reach this level of dynamic query support!!!

Best regards,
Juan Dent

[trueqbit]

@juandent Can you please review the examples in examples/common_table_expressions.cpp? I think they are already extensive enough.

[juandent]

    //WITH cte_1("n")
    //    AS(
    //        SELECT 'Alice'
    //        UNION
    //        SELECT "org"."name" FROM 'cte_1', 'org'
    //        WHERE("org"."boss" = 'cte_1'."n")
    //    )
    //    SELECT AVG("org"."height")
    //    FROM 'org'
    //    WHERE(
    //        "name" IN(
    //            SELECT 'cte_1'."n" FROM 'cte_1'
    //        )
    //    )

This one isn't coded.. Are the quotes necessary? I mean "org"can be just org correct?

Is this correct? it does not compile what am I doing wrong?

        constexpr auto cte_1 = "cte_1"_cte;
        constexpr auto n = "n"_col;
        auto ast = with(cte_1(n).as(union_all(select("Alice"), select(&Org::name, where(cte_1->*n == &Org::boss)))),
                        select(avg(&Org::height)),
                        where(in(&Org::name, select(cte_1->*n))));
``