[Retired] How to reference a derived table within the SQL of another derived table


#1

The content of this article has been updated and migrated to a Looker Help Center article.


Partial PDT Persistence
How are persistent derived tables named in the database?
#2

As of version 3.24, you can reference non-persistent derived tables in the SQL other derived tables as well! This uses the same syntax of:

${derived_table_view_name.SQL_TABLE_NAME}

Read more about this in our docs here.

Note that when you use this syntax in a derived table, make sure to alias the table name using AS.


(Ben Clark) #3

@lindsey is it possible for the SQL of the second derived table (which references the first view using .SQL_TABLE_NAME in the from clause) to also reference LookML-defined dimensions or measures from the first view?

For example, something like:

- view: pdt_1
  derived_table:
    sql: |
      select
        field_1,
        field_2
      from database_table

  fields:
  - dimension: field_3
    sql: ${TABLE}.field_1 + ${TABLE}.field_2
    type: sum
- view: pdt_2
  derived_table:
    sql: |
      select
        p.field_2,       --works
        ${pdt_1.field_3} --doesn't seem to work. is something like this possible?
      from ${pdt_1.SQL_TABLE_NAME} p
  …

#4

Hey Ben - it’s not currently possible to reference dimensions or measures defined in LookML in the SQL of a derived table. But I’ll talk to our product team about this! In the meantime, I recommend doing whatever transformation you’re doing in Looker field instead in a column in the derived table, so it can be referenced by another derived table.


#5

The functionality to reference dimension and measures defined in LookML in the SQL of a derived table would be very useful.


(Kay) #6

[edit] Never mind this comment. Misinterpreted it.


(Mike Ritchie) #7

+1 for adding Ben’s suggestion


(William Lane) #8

Thanks Mike! I’ll let the product team know


(Daan Visker) #9

I also +1 on that.
It would be extremely helpful to reference to defined dimensions from other views in the SQL of a derived table.
Since the view would already be used in the SQL, the need of this view to be JOINed on the resulting for its dimensions to become available feels redundant.