Oracle Case When Like

Question

I have the following code:

case when (a.je_source='Revaluation') then 'No_Location' 
    when d.user_name like ('SCHE%') then 'No_Location' 
    when d.user_name like ('C-FA%') then 'No_Location' 
    when d.user_name like ('C-AGO%') then 'No_Location'
    when d.user_name like ('C-VD%') then 'No_Location'
    when d.user_name like ('C-JL%') then 'No_Location'
    else d.user_name  
end as JE_User

Is there a way to make it cleaner? I tried the following and received a missing right parenthesis error.

case when (a.je_source='Revaluation') then 'No_Location'
    when d.user_name like ('SCHE%', 'C-FA%') then 'No_Location' 
    else d.user_name 
end as JE_User

Answer 1

As an option, you can use (oracle 10g and above) regexp_like condition:

     -- sample of data
SQL> with t1(je_source, user_name) as(
  2    select 'Revaluation1',  'SCHE123'  from dual union all
  3    select 'Revaluation2',  'C-FABCD'  from dual union all
  4    select 'Revaluation3',  'C-AGOABC' from dual union all
  5    select 'Revaluation4',  'C-VD'     from dual union all
  6    select 'Revaluation5',  'C-JLABC'  from dual union all
  7    select 'Revaluation',   'ABCDE'    from dual union all
  8    select 'Revaluation6',  'FGHIJ'    from dual
  9  ) 
 10  select je_source
 11       , user_name
 12       , case
 13           when je_source = 'Revaluation'
 14           then 'No_Location'
 15           when regexp_like(user_name, '^SCHE\w*|^C-FA\w*|^C-AGO\w*|^C-VD\w*|^C-JL\w*', 'i')
 16           then 'No_Location'
 17           else user_name
 18         end case
 19    from t1
 20  /

JE_SOURCE    USER_NAME CASE
------------ --------- -----------
Revaluation1 SCHE123   No_Location
Revaluation2 C-FABCD   No_Location
Revaluation3 C-AGOABC  No_Location
Revaluation4 C-VD      No_Location
Revaluation5 C-JLABC   No_Location
Revaluation  ABCDE     No_Location
Revaluation6 FGHIJ     FGHIJ

7 rows selected