CODEWITHSUNDEEP
snowflake-cloud-data-platform
ajcoder
ajcoder

Reputation: 237

Using ANY in CASE statement

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 ']'

Upvotes: 2

Views: 855

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175556

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;

Upvotes: 1

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25893

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

Upvotes: 0

Related Questions