Hello,
I have a derived table (with a filter), let’s call it child_table, that I want to be able to reuse in various ways in other derived tables (parent_table_1, parent_table_2)
I found this thread, which shows that you can reference a filter in a parent table from the child table. For example, you can do this:
view: first_pass {
derived_table: {
sql: select ...
from raw_data_table
where {% condition second_pass.some_field %} some_column {% endcondition %}
group by 1
;;
}
}
view: second_pass {
derived_table: {
sql: select ...
from ${first_pass.SQL_TABLE_NAME}
group by 1
;;
}
filter: some_field {}
}
So, in my case, I want to find a way that I could reuse the SQL in first_pass (or “child table”) (which contains the filtering) in more than just second_pass (the “parent table”). However, in this case, the filters in first_pass strongly references second_pass (by using second_pass.some_field). This means that first_pass can only be used by second_pass.
Ideally, I would want something like this:
view: child_with_filter {
derived_table: {
sql: select ...
from raw_data_table
where {% condition second_pass.some_field %} some_column {% endcondition %}
group by 1
;;
}
filter: some_filter {}
}
view: parent_view_1 {
derived_table: {
sql: select ...
from ${child_with_filter.SQL_TABLE_NAME}
-- some other SQL here
;;
}
view: parent_view_2 {
derived_table: {
sql: select ...
from ${child_with_filter.SQL_TABLE_NAME}
-- some other SQL here, different than parent_view_2
;;
}
I realize that one way to do this is by joining child_with_filter in an explore; however, my SQL is complex enough that I’m not sure I can achieve the same using JOINs.
The obvious issue is that the filter defined on the child_table is not in scope when it makes its way into the parent tables: I always get an error like:
“sql_render template processing error: variable not found ‘some_filter’”
Is there any way to achieve this?
By the way, I’m using the filters to pass a parameter to my child table. The filter ends up looking something like:
SELECT * FROM table where someDate < cast({% parameter some_filter %} as datetime)
