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).
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
Thanks, @conrad1!