Knowledge Drop

CASE in sql_on: Conditional Joins when field = value

Userlevel 1

Last tested: Sep 21, 2020

It's possible to create a conditional join based on a field returning a specific value.**

If liquid syntax isn't working, we can use a CASE WHEN in the sql_on parameter:

sql_on: CASE

WHEN view_name.field_name = "specific value"

THEN ${view_name.key_id} = ${view_name_2.key_id}

ELSE ${} = ${}

END ;;


**Note that this use case would be extremely rare unless we're also filtering on field_name = "specific_value" at all times in the explore page, such as in a sql_always_where filter (in which case, we would not even need the CASE WHEN statement). If field_name is just selected on the explore page without any filters applied, Looker will not know which join statement to perform because the statement will evaluate to TRUE for one field_name value but FALSE for other field_name values.

If you want to use this type of join, you would need to filter down your explore results by field_name at all times, such as with an access_filter, but somehow limit that access_filter to never accept more than one value. Perhaps using a parameter with allowed_values would also work in these scenarios.


This content is subject to limited support.                




2 replies

In your example, would the relationship also be dynamic to that condition? And would we just leave out the relationship key from the this explore’s join? How would that effect the results of your query?

Userlevel 3

Also note that in some dialects this kind of join condition can severely slow down your query.