Snowflake conditional on a CASE statement outcome

Question

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.

Answer 1

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: