[FIXED] How to create a new column using existing column in SQL Redshift

Issue

I am trying to create a new column in SQL with certain conditions in DBVisualizer by connecting redshift data source. I used the following command to achieve the results but getting syntax error. I would be really grateful for any help.

Code:

select existing_col, if(existing_col like '%LM%','LM',if(referrer like
'%LH%','LH',if(existing_col like '%HT%','HT',if(existing_col like '%M_App%','LM','Other 
Business Lines')))) 
AS derived_variable from
db.table_name;

Error:

[Code: 0, SQL State: 42883]  ERROR: function if(boolean, "unknown", "unknown") does not exist
  Hint: No function matches the given name and argument types. You may need to add explicit type casts.

select existing_col, 
if(existing_col like '%LM%','LM',if(existing_col like '%LH%','LH',if(existing_col like '%HT%','HT',if(existing_col like '%Mint_App%','LM','Other Business Lines')))) 
AS derived_variable from
db.table_name

Expected Output:

existing_col     derived_variable
PB                   Other
OTT                  Other
Mint_App             LM
LSLM                 LM
LMG                  LM
HTTC                 HT
KLH                  LH

Thanks.

Solution

As stated in the error message, the IF() function does not exist in Redshift. Therefore, you should use CASE:

select 
  existing_col, 
  case
    when existing_col like '%LM%' or existing_col like '%Mint_App%' then 'LM'
    when existing_col like '%LH%' then 'LH'
    when existing_col like '%HT%' then 'HT'
    else 'Other'
  end as derived_variable
from db.table_name

Answered By - Aleix CC

Answer Checked By - Marilyn (PHPFixing Volunteer)