conditional-statementssnowflake-cloud-data-platformsequel

Snowflake conditional on a CASE statement outcome


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.


Solution

  • 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:

    enter image description here