I am trying to see if the result of a case statement equals a specific value.
This does not work, but it should give you a good idea of what I am trying to accomplish, in this example, I am trying to see if it results in: 'some value'
SELECT IFF(
(CASE
WHEN SUBJECT = '' or SUBJECT is null THEN PROJECT_TYPE
WHEN DESCRIPTION != '.' THEN CONCAT(PROJECT_TYPE, ' ', DESCRIPTION)
ELSE DESCRIPTION
END) = "some value", 'yes', 'no')
FROM TABLE
I am not sure if this is even possible but thought I'd reach out, this would be a query being made in snowflake.
It is possible to compare case output against string literal:
SELECT (CASE
WHEN SUBJECT = '' OR SUBJECT IS NULL THEN PROJECT_TYPE
WHEN DESCRIPTION != '.' THEN CONCAT(PROJECT_TYPE, ' ', DESCRIPTION)
ELSE DESCRIPTION
END) = 'some value' AS result
FROM my_table;
The output value is boolean (true/false). For case insensitive comparison =
should be replaced with ILIKE
. The case could be further nested to get yes
/no
string:
SELECT IFF((CASE
WHEN SUBJECT = '' OR SUBJECT IS NULL THEN PROJECT_TYPE
WHEN DESCRIPTION != '.' THEN CONCAT(PROJECT_TYPE, ' ', DESCRIPTION)
ELSE DESCRIPTION
END) = 'some value', 'yes', 'no') AS result
FROM my_table;
Output: