CODEWITHSUNDEEP
sqlif-statementsnowflake-cloud-data-platform
Evoz
Evoz

Reputation: 23

IF,Else, then statement for a SQL snowflake database

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

Upvotes: 1

Views: 1935

Answers (1)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25893

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

Upvotes: 1

Related Questions