CODEWITHSUNDEEP
snowflake-cloud-data-platformcase
shivani bist
shivani bist

Reputation: 25

multiple CASE when statement

Are these two statements equivalent in snowflake?

Case 1:

CASE  
  when VAL='ABC' then 'ALPHA' 
  when VAL='123' then 'NUMERIC'
  else  'ALPHANUMERIC'
  end;

Case 2:

VOBJECTDESCRIPTION = 

CASE WHEN VAL='ABC'  THEN 'ALPHA' ELSE
  CASE WHEN VAL='123'  THEN 'ALPHA' ELSE   
  'ALPHANUMERIC' 
  END END;

Upvotes: 2

Views: 11211

Answers (1)

StriplingWarrior
StriplingWarrior

Reputation: 156524

Well, you've got several typos which make these both different, but given the gist of what you're trying to ask: statements like these both have the same behavior.

WITH X as (
    select VAL from (values ('1'), ('2'), ('ABC')) as x(VAL)
)
SELECT CASE  
  when VAL='ABC' then 'ALPHA' 
  when VAL='123' then 'NUMERIC'
  else 'ALPHANUMERIC'
  end
FROM X;

WITH X as (
    select VAL from (values ('1'), ('2'), ('ABC')) as x(VAL)
)
SELECT 
    CASE WHEN VAL='ABC' THEN 'ALPHA' 
    ELSE
        CASE WHEN VAL='123' THEN 'NUMERIC'
        ELSE 'ALPHANUMERIC'
        END
    END
FROM X;

From the parser's perspective, the first option thinks of all the WHENs as siblings, whereas the second one treats the entire second CASE statement as a child of the first ELSE. But I doubt there'd be any measurable difference in performance.

The first syntax is simpler, and I would favor it for readability. Even better, you can do this to avoid duplicative VAL= syntax:

WITH X as (
    select VAL from (values ('1'), ('2'), ('ABC')) as x(VAL)
)
SELECT CASE VAL
  when 'ABC' then 'ALPHA' 
  when '123' then 'NUMERIC'
  else 'ALPHANUMERIC'
  end
FROM X;

Upvotes: 4

Related Questions