Filters In Case When Statements

[intapiuser]

I came across an interesting use case with a customer where they had a name and ID column on a dataset: 

• Select a filter value for name, and not id -> show results for name
• Select a filter value for id, and not name -> show results for id
• If values are selected for both name and id -> show results that satisfy either filter condition.

Using a simple

where [id_column=id_filter] and [name_column=name_filter]

doesn't achieve the above behavior, especially since we want to show results that satisfy either condition if there are selections in both the id and name filters.

Instead, we want to leverage default filter notation and direct replacement filters in a case when statement:

SELECT *
FROM table
WHERE
CASE WHEN 'default' IN ('[name_filter|default]') THEN [id_column=id_filter]
WHEN 'default' IN ('[id_filter|default]') THEN [name_column=name_filter]
ELSE [id_column=id_filter] OR [name_column=name_filter] END

This SQL tells Sisense for Cloud Data Teams to use the id column if no value is passed in name_filter (and hence it returns 'default') and vice versa. If values are applied to both the name and id columns, the else clause is applied.