IF,Else, then statement for a SQL snowflake database

user_19301122 0
Viewed 134

Ive written this set of code which works in a typical SQL format, but when I try running it through snowflake database it errors out due to syntax. What would be my best approach to rearrange this statement to properly work for snowflake. thank you for your time!

   if [CUST_TYP] =  "Auction" THEN  "Auction"
    elseif  [CUST_TYP]  = "Broker / Reseller" THEN "Wholesale"
    elseif  [CUST_TYP]  = "Cat Dealer" THEN "Wholesale"
    elseif  [CUST_TYP]  = "Cat Dealer Rental Fleet" THEN    "Wholesale"
    elseif  [CUST_TYP]  = "Cat Private Auction" THEN "Auction"
    elseif  [CUST_TYP]  = "Customer In-territory" THEN "Retail"
    elseif  [CUST_TYP]  = "Customer out of territory, in country" THEN "Retail"
    elseif  [CUST_TYP]  = "Customer out of territory, out of country" THEN "Retail"
    else    [CUST_TYP] 
endif
snowflake-cloud-data-platform if-statement sql
1 Answers

You can use the CASE like:

case CUST_TYP
    when 'Auction' THEN 'Auction'
    when 'Broker / Reseller' THEN 'Wholesale'
    when 'Cat Dealer' THEN 'Wholesale'
    when 'Cat Dealer Rental Fleet' THEN    'Wholesale'
    when 'Cat Private Auction' THEN 'Auction'
    when 'Customer In-territory' THEN 'Retail'
    when 'Customer out of territory, in country' THEN 'Retail'
    when 'Customer out of territory, out of country' THEN 'Retail'
end

Also note in Snowflake double quotes are not valid strings, and are only valid for identifiers (table/columns names).

which when used like so:

select 
    'Auction' as cust_typ,
    case CUST_TYP
        when 'Auction' THEN 'Auction'
        when 'Broker / Reseller' THEN 'Wholesale'
        when 'Cat Dealer' THEN 'Wholesale'
        when 'Cat Dealer Rental Fleet' THEN    'Wholesale'
        when 'Cat Private Auction' THEN 'Auction'
        when 'Customer In-territory' THEN 'Retail'
        when 'Customer out of territory, in country' THEN 'Retail'
        when 'Customer out of territory, out of country' THEN 'Retail'
    end as chained_if
;

gives:

CUST_TYP CHAINED_IF
Auction Auction
user_439920
Related