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

CodePudding user response：

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: