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