CASE in sql_on: Conditional Joins when field = value

Knowledge Drop

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 ${view_name.id} = ${view_name_2.id}

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.                

Comments
zimzimma
New Member

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?

menashe
Participant V

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

Version history
Last update:
‎05-07-2021 09:48 AM
Updated by: