Case statement to calculated column

[Anonymous]

Hi all,

I have a scenario where I need to create a new column using case statement mentioned below, I tried using switch but it did not work any help would be highly appreciated.

 CASE
    WHEN publication in (101,100) then 'LAT'
    WHEN publication in (150,151) then 'SAN'
    END as property,
  CASE
    WHEN (isdigital = 1 OR iseedition = 1) AND isprint = 0 THEN 'Digital Only'
    WHEN (isdigital = 1 OR iseedition = 1) AND isprint = 1 THEN 'Bundle'
    ELSE 'Print Only' end
    as product_type,
  CASE
    When start_source = 'VO' THEN 'VO - Voluntary Customer Service'
    When start_source = 'SB' THEN 'SB - Subscriber Button'
    When start_source = 'ST' THEN 'ST - App Trial'
    When start_source = 'MD' THEN 'MD - Modal Sale'
    When start_source = 'EM' THEN 'EM - Internal Email'
    When start_source = 'DI' THEN 'DI - Digital Ad'
    When start_source = 'MC' THEN 'MC - Member Center Sale'
    When start_source = 'AD' THEN 'AD - Ad in Paper'
    When start_source = 'MP' THEN 'MP - Desktop Message Panel'
    When start_source = 'KS' THEN 'KS - Kiosk Sales'
    When start_source = 'DS' THEN 'DS - Door to Door Sales'
    When start_source = 'DM' THEN 'DM - Direct Mail'
    When start_source = 'VT' THEN 'VT - Vendor Telemarketing'
    When start_source = 'TM' THEN 'TM - Telemarketing'
    When start_source = 'IN' THEN 'IN - Internet'
    When start_source = 'SC' THEN 'SC - Single Copy Insert'
    When start_source = 'DC' THEN 'DC - Door Cash'
    When start_source = 'KC' THEN 'KC - Kiosk Door Cash'
    When start_source = 'AF' THEN 'AF - Affiliates'
    When start_source = 'EV' THEN 'EV - Event Sales'
    When start_source = 'SE' THEN 'SE - SEM sale'
    When start_source = 'IT' THEN 'IT - Internal Telemarketing'
    When start_source = 'MA' THEN 'MA - Mailer'
    When start_source = 'SO' THEN 'SO - Social Media Sale'
    When start_source = 'PT' THEN 'PT - Partnerships'
    When start_source = 'FL' THEN 'FL - Flyer External'
    When start_source = 'ED' THEN 'ED - External Display Ads'
    When start_source = 'WE' THEN 'WE - Winback Emails'
    When start_source = 'WS' THEN 'WS - Winback Social'
    When start_source = 'PE' THEN 'PE - Print Email'
    When start_source = 'AU' THEN 'AU - Auto Restart'
    When start_source = 'MT' THEN 'MT - Mather Economics'
    When start_source = 'CD' THEN 'CD - Commercial Delivery'
    When start_source = 'CO' THEN 'CO - System Correction'
    When start_source = 'JD' THEN 'JD - Joint Distribution Agreement'
    When start_source = 'DB' THEN 'DB - Database Price Increase'
    When start_source = 'G2' THEN 'G2 - Marketing G2'
    When start_source = 'VS' THEN 'VS - Paper Change'
    When start_source = 'SM' THEN 'SM - Sample Sale'
    When start_source = 'FC' THEN 'FC - Forced Conversion'
    When start_source = 'D ' THEN 'D - Default Source'
    When start_source = 'ZZ' THEN 'ZZ - Default from Conversion'
    When start_source = 'CS' THEN 'CS - Contract Sales'
    When start_source = 'DA' THEN 'DA - Decrease in Svc Did Not Order'
    When start_source = 'SS' THEN 'SS - Sample'
    When start_source = 'X ' THEN 'X - Conversion/Incorrect Value'
    When start_source = 'CA' THEN 'CA - Carrier'
    When start_source = 'DA' THEN 'DA - Decrease in Svc Did Not Order'
    When start_source = 'SY' THEN 'SY - System Correction'
    When start_source = 'BC' THEN 'BC - Broadcast'
    else start_source end as source_group,
  start_source,
  offer_source,
  promotion_id,
  promotion,
  offer,
  b.status,
  b.max_date,
  SUM(b.revenue) as revenue,
  count(CASE WHEN transaction_type = 'TRIAL' THEN temp_acct_num end) AS trial_orders,
  count(distinct CASE WHEN transaction_type = 'TRIAL' THEN temp_acct_num end) AS distinct_trials,
  count(CASE WHEN NOT transaction_type = 'TRIAL' THEN temp_acct_num end) as orders,
  count(distinct CASE WHEN NOT transaction_type = 'TRIAL' THEN temp_acct_num end) AS distinct_accounts,
  count(temp_acct_num) as total,
  count(distinct temp_acct_num) as total_distinct,
  count(circ_acct_num) as dsi_orders,
  count(distinct circ_acct_num) as dsi_accounts

[az38]

@Anonymous 

I, m too lazy to re-write all of your statement but use this logic when you create a Column:

property = SWITCH(TRUE(),
[publication]= 101 || [publication]= 100, 'LAT'
[publication]= 151 || [publication]= 150,'SAN'
)

then

product_type = 
SWITCH(TRUE(),
([isdigital] = 1 || [iseedition] = 1) && [isprint] = 0 , 'Digital Only',
([isdigital] = 1 || [iseedition] = 1) && [isprint] = 1. 'Bundle'.
'Print Only'
)

and

source_group = SWITCH([source_group],
'VO', 'VO - Voluntary Customer Service',
'SB', 'SB - Subscriber Button',
[start_source]
)

[Anonymous]

Thank you for the reply, you have stated the solution with three different columns i want them in a single column, if thats possible please let me know.