View the original community article here
Last tested: March 2021
The Problem
I need a condition to appear in the WHERE clause of my derived table, but I also need it to appear in the WHERE clause of my main Looker-generated query. So my final query should have the same condition in both places, like so :
WITH derived_table AS
( SELECT * FROM table WHERE <condition>)
SELECT * FROM derived_table
WHERE <condition>
Solution 1: Using a filter field
In Docs, we have a great example of using a filter field to pass a condition into a derived table.
view: customer_facts {
derived_table: {
sql:
SELECT
customer_id,
SUM(sale_price) AS lifetime_spend
FROM
order
WHERE
{% condition order_region %} order.region {% endcondition %}
;;
}
filter: order_region {
type: string
}
}
However, this only passes the condition into the derived table. The condition will not appear in the main query.
To have the condition appear in the main query, we take advantage of the fact that the sql
parameter of a filter field is placed directly into the WHERE
clause of the main query. By using templated filters in this sql
parameter, we apply the filter condition to any dimension we want.
view: customer_facts {
derived_table: {
sql:
SELECT
customer_id,
SUM(sale_price) AS lifetime_spend
FROM
order
WHERE
{% condition order_region %} order.region {% endcondition %}
;;
}
filter: order_region {
type: string
sql: {% condition order_region %} ${region} {% endcondition %} ;;
}
dimension: region {
type: string
sql: ${TABLE}.region ;;
}
Solution 2: Capturing a dimension's filter condition
Maybe you appreciate the solution above, but you don't like the extra step of creating an order_region
filter. Maybe you'd prefer to just filter directly on the region
dimension and have that filter condition also apply to the derived table.
This can be done by referencing the dimension name inside the condition
tags, like so!
view: customer_facts {
derived_table: {
sql:
SELECT
customer_id,
SUM(sale_price) AS lifetime_spend
FROM
order
WHERE
{% condition region %} order.region {% endcondition %}
;;
}
dimension: region {
type: string
sql: ${TABLE}.region ;;
}
Solution 3: (6.20+) Use NDTs and bind_all_filters
Maybe you want this behavior to apply to ALL of your filters. In that case, the above solutions will suffice, but will take a lot of time to implement. If you're using NDTs, an easy solution is to use the bind_all_filters parameter! There is an example use case in this community article.
This content is subject to limited support.