The Problem

I am writing derived tables that depend on each other. So, in my SQL I would like to reference the table name of an existing derived table. However, the persistent derived table names are stored in the database with a leading hash, which changes each time the derived table is rebuilt.

The Solution

There is a variable we can use that will always reference the name of the most recent version of the derived table. The syntax is: ${view_name.SQL_TABLE_NAME}, where view_name is the name of the view that contains the derived table. The string SQL_TABLE_NAME is a literal string and should be written exactly as it is; it’s not a variable that needs to be replaced by anything.

Example: Inside a Derived Table

This variable can be used in the sql parameter of a derived table. Note that this example spans two different view files:

view: example_1 {
  derived_table: {
  sql: SELECT field_a , field_b FROM table ;;
  }
}

view: example_2 {
  derived_table: {
  sql: SELECT field_a , field_b FROM ${example_1.SQL_TABLE_NAME} ;;
  }
}

If you need to reference a derived table to distinguish fields from the derived table, you can also use the ${view_name.SQL_TABLE_NAME} syntax as an alias, or just alias in the SQL: 

view: example_3 {
    derived_table: {
    sql: SELECT ${example_1.SQL_TABLE_NAME}.field_a , ${example_1.SQL_TABLE_NAME}.field_b 
       FROM ${example_1.SQL_TABLE_NAME} ;; 
  } 
} 

view: example_4 { 
   derived_table: { 
   sql: SELECT a.field_a , a.field_b FROM ${example_1.SQL_TABLE_NAME} as a ;; 
   } 
}

The ${view_name.SQL_TABLE_NAME} syntax can be used in most LookML parameters that accept SQL, as well as inside SQL Runner. This means it’s not necessary to use the actual hashed derived table name when developing!