Using CTEs in a dervied table

  • 30 July 2019
  • 1 reply

I am trying to use CTEs as part of my derived table SQL. The generated SQL puts the CTE definitions as part of a sub select which does not seem to work it seems to get ignored and I have no results. Is there a way to do this so the WITH clause is not part of an inner select ?

1 reply

Userlevel 1

I ran into this as well. The trick is to leverage Looker’s behavior of putting derived tables into a WITH and split the CTE into its own PDT that then is referenced by the first.

The “CTE” view doesn’t have to have any fields defined, since its only purpose is to work within the outer query.