Injecting extended view dimension in derived table sql doesn't work

[jcdufault]

Hi,

I built a future looking period control that has application in 4 different views. In the spirit of DRY, I built my lookml code in an independent view that I am extending into my 4 other views and I am using the sql_where declaration in my join to inject the proper clause.

Setting up the code just like table_a works well, but when I have a setup like table_b, the sql_always_where_inject reference won't work in the derived table declaration. I can't get the sql_always_where_inject to show up. 

## Explore File
explore: my_explore {
  join: table_a {
    sql_on: ${my_explore.id} = ${table_a.id} ;;
    type: inner
    relationship: one_to_many
    sql_where: ${table_a.sql_always_where_inject} ;;
  }
  join: table_b {
    sql_on: ${my_explore.id} = ${table_b.id} ;;
    type: inner
    relationship: one_to_many
  }
}

## view_a / table_a
include: "/future_period_controls.view"
view: advance_campsite_data {
  sql_table_name: schema.table_a ;;
  ##LookML here
  extends: [future_period_controls]
}

## view_b / table_b
include: "/future_period_controls.view"
view: table_b {
  derived_table{
    #Can't get the sql_always_where_inject to show up
    sql: select * from schema.table_b where ${table_b.sql_always_where_inject} ;;
  }
  ##LookML here
  extends: [future_period_controls]
}

## future_period_controls.view
view: future_period_controls {
  extension: required
  
  dimension: sql_always_where_inject {
    sql: --SQL GOES HERE ;;
  }
}

I tried referencing it in liquid or with the original view name `${future_period_controls.sql_always_where_inject}` and both didn't work. 

Is there a way to achieve what I'm trying to do or will I have to depart from a derived table (which I would very much like to avoid). 

Thank you!

[Kevin_F_McCarth]

Hello jcdufault, I love to see these clever approaches in the spirit of DRY.

TLDR:  I was able to test and confirm that you should be able to use liquid likein the derived table to pull in raw sql from a dimension. 

sql: select * from schema.table_b where ${table_b.sql_always_where_inject} ;;

with:

---

More info and caveats

I'm am not sure the reasons and/or history, but derived table sql does not allow standard field references (as you observed).  When I tested it with any field reference, I get the warning 'Unknown Substition Operator'.  

That said, there is a little known liquid variable you may be able to leverage, which I have used in some scenarios like this: [field_name]._sql (within double curly braces). 

is very similar to ${} but you can use it to then apply liquid tags/functions on the generated sql.  It also works in a few niche cases like yours where standard references aren't allowed. 

I should call out that it is not called out in official documentation and so could theoretically stop working in some future version.  That said, I believe it has been functioning as is for many years at this point. 

One other callout: if the field who's sql you are referencing is itself dynamic and references other views/fields not used by the query, or if sql is driven by the type (e.g. filtered measure), the ._sql call may not pull the fully resolved sql. But I don't think that is a blocker for your scenario.