04-07-2017 01:15 PM
Hi, I'm using a case else statement to try to create a new variable (source_flag) to fill in for blank for the first_source variable, however it is not populating the new variable definition of 'unknown' when it runs. Any help would be greately appreciated.
create table d02_buyer_source_recency as
select DISTINCT email_id,
/* first source */
CASE when (first_source = ' ' OR first_source = '.') then 'UNKNOWN'
ELSE first_source END as Source_Flag
from oralib.sum_email_list_vw ;
04-07-2017 01:30 PM
It's not fully clear without seeing your data, but you could try:
CASE first_source WHEN '' THEN 'UNKNOWN'
WHEN '.' THEN 'UNKNOWN'
END AS Source_Flag
04-08-2017 01:07 AM
Make sure your character variable is really empty. It might just look empty, but really include non-printing characters like tabs, line feeds, carriage returns, form feeds, non-breaking spaces, nulls.
Also it might look like it has just a period in it, but it really has a period that follows one or move spaces.
CASE when compress(first_source) in (' ','.') then 'UNKNOWN' ELSE first_source END as Source_Flag