Two WITHs

[menashe]

If I write a PDT whose SQL starts with with foo as and include in the PDT a reference to another non-persistent derived table as ${non_pdt.SQL_TABLE_NAME}, then Looker generates the SQL with non_pdt as(...) with foo as, a syntax error (correct syntax is with non_pdt as(...), foo as).

There are of course a couple of workarounds: 1, make non_pdt into a PDT instead (if possible); 2, start the PDT’s SQL with , foo as (with comments explaining that it’ll need to be changed if foo is changed to a PDT). But this seems to be a bug. Possible fix: if a PDT starts with with, then create foo as a temporary table rather than as a CTE (which is what you already do in dialects that don’t have CTEs).

[conrad1]

Hi Menashe.
The correct approach is what you refer to as workaround 1. Decompose the single PDT into a PDT and one or more non-persistent derived tables. As an example,

view: depends_on_intermediate {
  derived_table: {
    datagroup_trigger: "some_datagroup"
    sql: with intermediate as (select foo, bar from something)
           select foo, bar, baz from other join intermediate ;;
}

becomes

view: intermediate {
  derived_table: {
    sql: select foo, bar from something ;;
}
view: depends_on_intermediate {
  derived_table: {
    datagroup_trigger: "some_datagroup"
    sql: select foo, bar, baz from other join ${intermediate.SQL_TABLE_NAME} ;;
}

Note that intermediate is not persisted

[menashe]

Thanks, @conrad1!