Using ANY in CASE statement

user_2224600 0
Viewed 194

I have to implement logic where I need to use ANY in case statement. But I am not sure if this is allowed in Snowflake. The code looks like

SELECT CASE 
WHEN (Field1 = ANY(array['a'::VARCHAR(24), 'b'::varchar(15), 'c'::varchar(20)])) THEN 'd'::varchar(16)
WHEN Condition 2 THEN abc
ELSE def
END AS xyz

Getting syntax error Unexpected ',' and unexpected ']'

snowflake-cloud-data-platform
2 Answers

assume you are referring to LIKE ANY but want it in the SELECT/CASE,

a CASE is actually almost the perfect form, as instead of saying any one of these, you can just "repeat" the WHEN clause of each of the valid instances.

Thus the original:

SELECT 
    CASE 
        WHEN (Field1 = ANY(array['a'::VARCHAR(24), 'b'::varchar(15), 'c'::varchar(20)])) THEN 'd'::varchar(16) 
        WHEN Condition 2 THEN abc 
        ELSE def 
     END AS xyz

can be written as:

SELECT 
    CASE 
        WHEN Field1 = 'a'::VARCHAR(24) THEN 'd'::varchar(16) 
        WHEN Field1 = 'b'::varchar(15) THEN 'd'::varchar(16) 
        WHEN Field1 = 'c'::varchar(20) THEN 'd'::varchar(16) 
        WHEN Condition 2 THEN abc 
        ELSE def 
     END AS xyz
user_439920

Using IN:

In subquery form, IN is equivalent to = ANY and NOT IN is equivalent to <> ALL.

SELECT CASE WHEN Field1 IN ('a'::VARCHAR(24), 'b'::varchar(15), 'c'::varchar(20))
            THEN 'd'::varchar(16) 
            WHEN Condition 2 THEN abc 
            ELSE def 
    END AS xyz
FROM tab;
user_50708790
Related