Comparing several fields against a list of strings

Hi There,

I have several field:

alert_acked_by
alert_closed_by
alert_owner

I’d like to write a view that checks to see if any of these fields match a list of string, eg
(“John Smith”, “Jane Doe”, “Sue Applebee”)

What is the best way to write this, eg most idiomatic, best practice?

sql: CASE WHEN
${alert_acked_by} OR
${alert_closed_by} OR
${alert_owner} IN (“John Smith”, “Jane Doe”, “Sue Applebee”)
THEN “Their Manager”

is not accepted.

Thanks for any pointers!

Try
sql: CASE WHEN
${alert_acked_by} IN (“John Smith”, “Jane Doe”, “Sue Applebee”)
OR ${alert_closed_by} IN (“John Smith”, “Jane Doe”, “Sue Applebee”)
OR ${alert_owner} IN (“John Smith”, “Jane Doe”, “Sue Applebee”)
THEN “Their Manager”

1 Like

Thanks Zack. That does work, but then I’m duplicating the declaration of the hard coded strings. Any way of avoiding this?

I don’t think so, it’s standard SQL syntax.

Operators (OR, AND, NOT) should be placed between conditions, not values. Although it makes sense to a human to say This OR That = Thing, SQL doesn’t understand. Check out https://www.w3schools.com/sql/sql_and_or.asp for a better explanation.

What if you tried using the newer constant feature?

https://docs.looker.com/reference/manifest-params/constant