How to create a new column using existing column in SQL Redshift

Question

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.

Could you specify a bit more on the issue? Provide the syntax error, a sample of the data and the desired output of the query would help. — Aleix CC, Jul 11 '22 at 08:55

score 0 · Accepted Answer · answered Jul 11 '22 at 12:29

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

How to create a new column using existing column in SQL Redshift

1 Answers1