WITH...AS clauses break with non-persistent derived table assets

[davidbo]

When a PDT is built with a “WITH…AS” clause at the start of the SQL definition, any non-persistent derived tables used as assets (by adding them with “includes”) will cause the generated SQL to break. This is because the asset table is created using a “WITH…AS” clause, and also the PDT keeps it’s “WITH…AS”, so you end up with something like the following:

CREATE TABLE looky_here.LR$T5SL71601481443478__view_my_table DISTSTYLE ALL SORTKEY(pk) AS WITH _table_users AS (SELECT
        ...
      FROM
        ...
      LEFT JOIN ...)
WITH audit AS (...

As you can see, there are two “WITH…AS” clauses which is not allowed (at least in Redshift). The workaround is to change the source table to also be a PDT, however this is not ideal.

Would it be possible to fix it so that only one “WITH…AS” clause will be present in the generated SQL at one time?

[menashe]

https://community.looker.com/sql-10/two-withs-11384