How to combine CTE and UNION ALL?

[peczenyj]

Hello

I am trying to use sq to generate dynamic queries and I need to generate a complex combination of WITH (cte) and several queries via UNION ALL

example

WITH … AS foo
SELECT … FROM foo … UNION ALL
SELECT … FROM foo … UNION ALL
…

based on the documents I am able to create it in two parts using different object builders but I can create everything at once. Any suggestions?

full example

WITH overlap_table (C1, C2, xxx) AS (
	SELECT T1.C1, T1.C2, table1.xxx
    FROM
        table1 T1,
        table2 T2
    WHERE
        T1.xxx = T2.xxx
)
SELECT C1 AS T1_C1, C2 AS T1_C2, COUNT(xxx) AS overlap
FROM
    overlap_table
GROUP BY (C1, C2)
UNION ALL
SELECT C1 AS T1_C1, NULL AS T1_C2, COUNT(xxx) AS overlap
FROM
    overlap_table
GROUP BY (C1)
UNION ALL
SELECT NULL AS T1_C1, C2 AS T1_C2, COUNT(xxx) AS overlap
FROM
    overlap_table
GROUP BY (C2)
UNION ALL
SELECT NULL AS T1_C1, NULL AS T1_C2, COUNT(xxx) AS overlap
FROM
    overlap_table
GROUP BY ALL
;

[bokwoon95]

Umm can you describe which parts of this query will be dynamic? As you have probably already observed, the built-in CTEs and UNION (ALL) weren't designed to work together so you might have to do a little more hardcoding with Queryf(). It's still possible to add dynamic-ness to Queryf() queries though, I just gotta know which parts of the query will be dynamic.

• I'm assuming the number of queries to UNION ALL will be dynamic.
• Are the number of CTEs dynamic?
• Are the fields being selected dynamic?